Accumulating A Value from Mulitple Tables

[EddieN1]

How to I accumulate a value that exists in multiple tables. Example, I want to determine inventory on hand for a particular Partnumber based on InventoryAdjustments (InvAdj), Receipts (InvReceipts), and Sales (InvSales). Each table contains a date, Partnumber, and Quantity (Qty). The Qty in InvAdj whose date is the Fiscal Year Start Date represents the quantity from the last Physical Inventory. How can I write a query that will go through all tables from the Fiscal Year Start Date for a particular Partnumber and return the total Qty of all three tables. The total should ADD Qty from InvAdj and InvReceipts and SUBTRACT Qty from InvSales.



Thanks, Eddie

[jzwp11]

You might want to take a look at Allen Browne's site and his discussion of Inventory Control: Quantity on Hand

[EddieN1]

Thanks. I was looking for a query but the Function will work as well or better.

[jzwp11]

You're welcome.