[learn_n_impart]

Hi All!

I am using MS Access 2010. I am creating a form that has a field name 'BookValue'. The first 'BookValue' is equal to AcquisitionCost - MonthlyDepreciation. The next BookValue is equal to previous BookValue - MonthlyDepreciation.

Acquisition Cost 10000

Asset ID Date of Depreciation Monthly Depreciation Book Value
1001 1/15/2012 1000 9000
1001 2/15/2012 1000 8000
1001 3/15/2012 1000 7000

I tried using SUM function, but the values of BookValue are all the same.

Can you suggest how I should go about it?

Thanks a lot.

[June7]

This is easier on a report. Textboxes on report have a RunningSum property.

Running sum on a form requires calculation to get the sum value of all records up to and including the current record. One way is with a subquery. Review http://allenbrowne.c...ubquery-01.html

Assume depreciation records like:
AssetID Amount DepDate
1 1000 1/1/2012
1 1000 2/1/2012
1 1000 3/1/2012
2 500 1/1/2012
2 500 2/1/2012
3 700 1/1/2012

This query produces running difference (I just used 20000 as the value at new condition for all assets, you would get this value by join to the asset info table):
SELECT Dep.*, (SELECT Sum(Amount) AS SumDep FROM Dep AS Dupe WHERE Dupe.[ID] <= Dep.[ID] AND Dupe.AssetID = Dep.AssetID) AS SumDep, 20000 AS NewVal, [NewVal]-[SumDep] AS BookVal
FROM Dep;

However, this results in an uneditable query so no good as RecordSource for a data entry form. An alternative uses DSum instead of the nested subquery:

SELECT Dep.*, 20000 - DSum("Amount","Dep","AssetID=" & [AssetID] & " AND ID <=" & [ID]) As BookVal FROM Dep;

This post has been edited by June7: 31 March 2012 - 01:51 PM