Calculating Units on Hand

[Dave14867]

Hello all,



I am trying to calculate a "Units on Hand" field from a query and haven't had any luch, 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.

Can someone please assist.


Dave -In Process.zip


Thanks

Dave

[Daisy509th]

To make the field in the query you would type it this way "QtyonHand:[UnitsReceived]-[UnitsSold]-[UnitsShrikage]" of course your query would be build off of the Transaction Table with the three units mentioned in the query. I hope this helpsAttachment 33091Attachment 33092

[orange]

This Allen Browne article discusses Quantity on Hand and many other concepts of Inventory Management.

[Dave14867]

Daisy509th,

I can't seem to get that to work, it wants input for the "UnitsShrinkage". If I leave it blank the "QtyonHand" is blank.

Orange,

I have read that but I just don't know where to put that code. I am an extreme novice at VBA code right now.

Thanks

Dave

[orange]

Did you look at the table structure and code in the article?

In general:

QtyOnHand: QtyAtLastStockTake +QtyAquired-QtySold

[Daisy509th]

If you put Nz(fieldname,0)-Nz(fieldname,0) Then your math will work and you wont have to have anything enter into table. The formula I should you goes in the query

Hope this helps

QtyonHand:Nz(UnitsReceived,0)-Nz(UnitsSold,0)-Nz(UnitsShrikage,0)

[Dave14867]

Daisy509th,

Here is what I put into the field I created in the "Assembly Parts List with Build Qty from WO Table" query , QtyonHand: Nz([UnitsReceived])-Nz([UnitsSold])-Nz([UnitsShrikage]), and if you run that query (I view it so as not to actually run it but just see the results) it for the "Parameter Value" of "UnitsShrinkage" and if I hit OK or enter "0" it the asks for the "WO Number" which is "test" and the results show no value in the last field.

[orange]

You spelled Shrinkage without an n

[Daisy509th]

I made a query with the table "Inventory Transactions-test" and add this to the end of query "QtyonHand:Nz(UnitsReceived,0)-Nz(UnitsSold,0)-Nz(UnitsShrinkage,0)" and the query report all fields properly

Hope this helps

Thanks Orange for the catch

[Dave14867]

orange,

your correct, don't know how I missed that, Thank You.

Daisy509th,

Delete the table "Inventory Transactions-test", I had that in there testing something else out. I should have deleted it before zipping it, my error.

If you add that to the end on "Assembly Parts List with Build Qty from WO Table" query and enter the WO of "test" you'll get the results which are incorrect, the "QtyonHand" field at the end is all "0" which is incorrect, that is the issue I have been having right along. Am I missing something?

Thanks

Dave

[Daisy509th]

Dave,

I believe it to be in the query design more specifically the joins. I am sorry but this is going to be beyond me sir. I truly am sorry but I am sure this is above my experience level.

[Dave14867]

Daisy509th,

Thanks for trying, but it appears you hit the same issue I had, it seems like it should work but doesn't. It's over my head at this time too.

Can someone else take a look and see what we are doing incorrectly?


Thanks

Dave

[orange]

Dave,

Did you test your model with some sample data and scenarios before building the physical database?
I adjusted your model to show the tables fully. I'm not sure that your tables or relationships depict your business. You know the facts better than any reader.


Your PurchaseOrders identify the ItemsAcquired, and it seems your WorkOrders would identify the ItemsUsed.

If you work with the Transaction table where ItemsAcquired are positive. And ItemsUsed are negative.
You could work with a few parts to confirm the counts and your logic.

Also it is better to not have embedded spaces in field names. And no special characters eg(#). They require special formatting and usually lead to problems.

[Dave14867]

Orange,

Whenever a receipt of a part comes in, it gets an entry in the "Inventory Transactions" table, and the same for when an item is consumed in an assembly (Thats what the "Inventory Transactions - test" table was testing out), so I believe everything is tracked by the "Inventory Transactions" table, unless I am goofing something up.

Thanks

Dave

[orange]

Can you work with the transaction table alone? Consider only 1 part?

Incoming/acquisition is a Positive TransType, Outgoing/usage is Negative.

For a given Part QtyOnHand = LastStockTakeQty +IncomingQty - OutgoingQty