prabuTaila
New member
- Local time
- Today, 02:04
- Joined
- Nov 16, 2019
- Messages
- 3
Hello Friends,
I am developing a database for Asset Control to apply to my project.
The Company Setup:
There are many Construction projects are going on in the Company,
Each Project receiving Assets & Materials from various sources.
I use to receive assets from various custodian within the Company. for example, Lab, Scaffolding and etc.
At the completion of project, I use to return the reusable assets to the Custodians, Scrap the damaged assets and write-off the missing assets.
Now the Query Part.
Receive Query: CustodianNo, ProjectNo, RevRefNo, AssetNo, RevAssetQty
Return Query: ProjectNo, RtnRefNo, RevRefNo, CustodianNo, AssetNo, RtnAssetQty
Scrap Query: ProjectNo, ScrapRefNo, RevRefNo, CustodianNo, AssetNo, ScrapAssetQty
Write-Off Query: ProjectNo, writeoffRefNo, RevRefNo, CustodianNo, AssetNo, writeoffAssetQty
How to calculate Assets On Hand based on the project?
Assets on Hand = SumofRevAssetQty- SumofRtnAssetQty-SumofScrapAssetQty-Sumof writeoffAssetQty
I can do calculation by using two queries using left join.
But, I couldn't do it using four queries.
Please Support.
Prabu.
I am developing a database for Asset Control to apply to my project.
The Company Setup:
There are many Construction projects are going on in the Company,
Each Project receiving Assets & Materials from various sources.
I use to receive assets from various custodian within the Company. for example, Lab, Scaffolding and etc.
At the completion of project, I use to return the reusable assets to the Custodians, Scrap the damaged assets and write-off the missing assets.
Now the Query Part.
Receive Query: CustodianNo, ProjectNo, RevRefNo, AssetNo, RevAssetQty
Return Query: ProjectNo, RtnRefNo, RevRefNo, CustodianNo, AssetNo, RtnAssetQty
Scrap Query: ProjectNo, ScrapRefNo, RevRefNo, CustodianNo, AssetNo, ScrapAssetQty
Write-Off Query: ProjectNo, writeoffRefNo, RevRefNo, CustodianNo, AssetNo, writeoffAssetQty
How to calculate Assets On Hand based on the project?
Assets on Hand = SumofRevAssetQty- SumofRtnAssetQty-SumofScrapAssetQty-Sumof writeoffAssetQty
I can do calculation by using two queries using left join.
But, I couldn't do it using four queries.
Please Support.
Prabu.