Hi everyone,
I am playing around with my DB again and now have 2 tables:
I want to be able to run a query that shows all the items in the Inventory and their "current" stock quantity - I.e. it shows the value of the Opening stock plus the sum total of all transactions for "that" PartID in "that" WHLocatID where there is a transaction against it. If no transactions have been made against "that" Part, in "that" location, then just the Opening Stock is shown.
So a rather long and painful way of saying that what I want is:
Many thanks in advance for all your help.
Chris
I am playing around with my DB again and now have 2 tables:
- "Inventory" which has the following fields: PartID, WHLocatID and OpeningQty. PartID is unique, WHLocatID is the location of the item in our warehouse (not unique) and OpeningQty is simply the quantity we started with
- "TransactionLog" which has several fields but the main ones for this question are: PartID, Origin, TransactionQty. Origin in this case would match WHLocatID from the Inventory Table and TransactionQty is simply a quantity from a transaction - this is negative for an Issue and positive for a Receipt.
I want to be able to run a query that shows all the items in the Inventory and their "current" stock quantity - I.e. it shows the value of the Opening stock plus the sum total of all transactions for "that" PartID in "that" WHLocatID where there is a transaction against it. If no transactions have been made against "that" Part, in "that" location, then just the Opening Stock is shown.
So a rather long and painful way of saying that what I want is:
- To list the current stock quantity of all items in the inventory.
- The current stock quantity should be equal to the Opening Quantity Plus any transaction quantities where the transaction Origin matches the WHLocatID AND the transaction PartID matches the (inventory) PartID.
- If no transactions have been made against the exact combination of WHLocatID and PartID that is shown in the Inventory table, then the current stock quantity shown should just be the "opening" quantity value.
Many thanks in advance for all your help.
Chris