Calculating Units on Hand (1 Viewer)

Hello all,

I posted this on another forum about a week or so ago and still have no answer that works so I thought I'd post it here to see if I get other replies that may solve it for me.

I am trying to calculate a "QtyonHand" field from a query and haven't had any luck, I am not sure what I am missing or doing incorrectly. In the attached file, if you run the "Assembly Parts List with Build Qty from WO Table" query and enter the WO of "test" you'll get the results which are correct. I have tried to add a field at the end for "Qty on Hand" and haven't been able to get it. I would like to pull the info from the "Transaction Table" by adding the "UnitsReceived"-"UnitsSold"-UnitsShrikage". I have tried several times and I can't get the value to show.

If working, the following Qty's should appear in the "QtyonHand" field of the query results.

M23599 - 0
M19799 - 12500
M19800 - 16500
M19801 - 54000
M19802 - 54000
M19803 - 54000
M19801 - 1193500
M19809 - 105

Any help greatly appreciated. I have been trying to solve this off and on for a week with no luck yet. SQL is not my strong point by any means.

Ultimately I want to include this in the report "Parts List by Work Order" and the Form "Assembly Parts List With Build Qty from WO Table1"




Thanks

Dave

I remember seeing it at the other forum & know you had some replies.
Please provide the link so anyone interested can check what has already been suggested

ridders,

Here is the link to the other site.

I should also add that I don't know how to incorporate Allen Browns code.

http://www.accessforums.net/showthread.php?t=71087


Thanks

Dave