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

there are 2 records in this because the Key and Date fields are included to be grouped upon....

for a single sum remove those 2 fields from the query....

note that this still means the Type field is a grouping element....and may or may not be what you seek....to get an overall sum including differing Types then this field must be removed also...

I would suggest you rearrange so that all group fields are to the left of the sum field....

also keep in mind, as previously posted...that a Report has grouping as well - - it all depends on how and where you wish to present the data...

Answer 2

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));

Answer 3

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 4

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 5

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.