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

Khurram 1 Reputation point
2021-01-01T15:36:20.71+00:00

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.

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,500 questions

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. C Data 6 Reputation points
    2021-01-11T13:26:03.567+00:00

    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...

    0 comments