How to calculate Assets on Hand=Rev-Rtn-Scrap-Writeoff? (1 Viewer)

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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:04
Joined
May 7, 2009
Messages
19,170
select AssetNo, (Select Sum(RevAssetQty) From [Receive Query] As T Where T.AssetNo =MasterAssetTable.AssetNo) -
(Select Sum(RtnAssetQty) From [Return Query] As T Where T.AssetNo = MasterAssetTable.AssetNo) -
(Select Sum(ScrapAssetQty) From [Scrap Query] As T Where T.AssetNo = MasterAssetTable.AssetNo) -
(Select Sum(writeOffAssetQty) From [Write-Off Query] As T Where T.AssetNo = MasterAssetTable.AssetNo)
from MasterAssetTable;
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 09:04
Joined
Sep 12, 2006
Messages
15,614
this might be a data structure issue.

Just have a table showing "plantID, Allocated project, date started, date completed. "
Or maybe more correctly "plantID, Allocated project, date, action type" to store plant being allocated start, and then either sold, scrapped or allocation completed.

You don't really want multiple plant tables, one for each location.

All you need is a couple of checks

don't allow any part to be allocated to a new project unless the last allocation has terminated,
get the custodian to agree to the termination of the current allocations, as if it's not still out on site, it must be with the custodian. (or lost/scrapped/stolen)

then plant is located based on the latest allocation record. Either out on site, or gone, or in store (if it's not out on site)
 

prabuTaila

New member
Local time
Today, 02:04
Joined
Nov 16, 2019
Messages
3
select AssetNo, (Select Sum(RevAssetQty) From [Receive Query] As T Where T.AssetNo =MasterAssetTable.AssetNo) -
(Select Sum(RtnAssetQty) From [Return Query] As T Where T.AssetNo = MasterAssetTable.AssetNo) -
(Select Sum(ScrapAssetQty) From [Scrap Query] As T Where T.AssetNo = MasterAssetTable.AssetNo) -
(Select Sum(writeOffAssetQty) From [Write-Off Query] As T Where T.AssetNo = MasterAssetTable.AssetNo)
from MasterAssetTable;
Thanks for your reply.
I am not clear your Code. Please check the attached Query relation and advice me.
 

Attachments

  • Access On Hand.png
    Access On Hand.png
    47.5 KB · Views: 130

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:04
Joined
Feb 19, 2002
Messages
42,981
If you enter the inventory transactions correctly, it is a matter of summing the values. Each quantity should be stored with a sign so quantities that add to inventory are positive numbers and transactions that subtract from inventory are negative. Adjustments can be positive or negative depending on their intent. Then you run a totals query and sum the quantity field.
 

Users who are viewing this thread

Top Bottom