Access - Need help to calculate and store assets current book value

Question

Hello everyone, I have created a database to maintain records of the assets. For the time being, the database contains following 3 tables,

tblAsset
AssetID
AssetName
AquisitionDate
AquisitionValue

tblAssetAdd
AssetAddID
AssetID
AssetAddDate
AssetAddValue

tblScrap
ScrapID
AssetID
ScrapDate
ScrapValue

I created following query to calculate assets current book value.

SELECT Asset.AssetID, Asset.AssetName, Asset.AquisitionDate, Asset.AquisitionValue, [AquisitionValue]12/100 AS AnnualDep, ([AnnualDep]/365)(Date()-[AquisitionDate]) AS AccumulatedDep, [AquisitionValue]-[AccumulatedDep] AS [Current Value] FROM Asset;

The query is calculating current book value of the asset up to the current date because I am using Date() function in the formula of the AccumulatedDep.

I need help to store and calculate current book value as described below,

• There are possibilities that assets or value of the assets added or scrap on the later dates and hence I created tblAssetAdd for addition and tblScrap for subtraction.
• First, I want to calculate current book value of the asset up to date of the AssetAddDate or ScrapDate. If the asset is added or scrapped the current book value should be calculated further and also store in the table. I believe to calculate and store current book value I will have to create another table e.g. tblDepreciation.
• The purpose of calculating current book value of the asset is that I want to create a query to display monthly / yearly current book value. Thanks for your help and guidance.

Thanks for your help and guidance.

Answer 1

in is my view that your schema should differ so that the Asset table is more generic, and includes the Add/Scrap records i.e.:

tblAsset
AssetKey - autonumber
AssetID - does your organization assign IDs to assets?
AssetName
ActionDate
ActionValue
ActionType - Acquisition, Add, Scrap

Then you can group by AssetID (or by AssetName if truly always unique) and the ability to sum a total at any date is very simple.

You can present/report Acquisitions vs Adds vs Scraps easily & separately by report/query criteria based on the Action Type. ... such that users would not know, nor need to know, that the data is held in a common table.

this is a fairly standard approach to inventory management, and in general it is better to calculate & present a sum rather than calculate & store a sum, as in terms of best practices one must then be very careful that a stored sum does not become out of sync with changing data....

Answer 2

Thanks for responding. I got your point, I will create a table as per your suggestions. But how to handle multiple entries for same Asset ?

Suppose if I create table as you mentioned in your comments so in this case every Asset would have multiple records e.g. First Acquisition then Add or Scrap and so on.

Answer 3

Thanks for responding. I got your point, I will create a table as per your suggestions. But how to handle multiple entries for same Asset ?

Suppose if I create table as you mentioned in your comments so in this case every Asset would have multiple records e.g. First Acquisition then Add or Scrap and so on.

Answer 4

how to handle multiple entries for same Asset ? ...

• well that is a generic question, and the answer depends on what is meant by 'handle' ....but i.e. sum the current value of Asset 123 one would query the table for that Asset ID so that the records only contain Asset 123..... and then one can use an Aggregate query to sum... or use the query as the basis for a report and have the sum occur in the report.

Answer 5

I have created a sample database to show you the multiple entries. Below is the screenshot and SQL view of the Aggregate query,

SELECT tblAsset.AssetKey, tblAsset.AssetID, tblAsset.AssetName, tblAsset.ActionDate, Sum(tblAsset.ActionValue) AS SumOfActionValue, tblAsset.ActionType
FROM tblAsset
GROUP BY tblAsset.AssetKey, tblAsset.AssetID, tblAsset.AssetName, tblAsset.ActionDate, tblAsset.ActionType
HAVING (((tblAsset.AssetID)="CH1") AND ((tblAsset.ActionType)=2));