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.