I am trying to set up a query which calculates the total depreciation of each asset in an asset table over a number of consecutive financial years. I am using the reducing balance method which calculates the pro-rata depreciation of each asset each financial year with a starting value in each year equal to the closing value in the previous.
So, mathematically, you have a “piece-wise” linear function.
In Excel it was easy.
Your assets table has colums for
purchase price,
purchase date,
sale price (if any),
sale date( if any),
depreciation rate % per year,
and a row for each asset.
From this you could contruct an Excel list where the depreciation could be calculated for each asset and shown in its own column. Then you get the closing value by
subtracting the depreciation from the opening value for the year in question. For each new financial year I created a new page in the workbook and transferred the closing value over to the new year’s opening value for each asset.
Now!
I wan’t the whole thing to now work in Access.
You can create a query based on the Assets table but
at some point I keep coming up against the problem of taking
values from previous rows of the query to use in the current row. I read and applied the Microsoft KB article
“Q210504 – ACC2000 Referring to a Field in the Previous Record or Next Record.htm” which was enlightening but doesn’t seem to lead to a solution.
Has anyone any ideas how to proceed?
