I have a query that uses a table containing 30 years of data for 75 locations. The data is as follows Location #, Rent $'s, Interest $'s, Depreciation $'s obtained from a table named "Capital Information" and NBV and Remaining Debt as of 2007 obtained from "Capital Information NBV" Currently I have the query set to return data for ONE year only, i need to alter it so I can report on all 30 years in one report with subtotals.
I need to return the following calculated fields
Field 1 Debt Repayment = [Capital Information]![Rent]-[Capital Information]!Interest
Field 2 Debt as of Year End for each of the 30 years by location = [Capital Information NBV]![Remaining Debt 2007]-a running sum of [Debt Repayment] field calulated above.
Field 3 NBV as of Year End for each of the 30 years by location = [Capital Information NBV]![NBV as of 2007]-a running sum of [Capital Information]![Depreciation $'s]
I have the query set up right now so I enter a Year and it will return the above information for each location correctly. But I do not know how to set it up to give me all 30 years on one report, while calulating a total Debt Repayment, Debt as of Year End, and NBV as of year end for each Year.