Solved Am I looking for a union query? (1 Viewer)

[ChrisC]

Hi everyone,

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.

Each PartID can be in many locations and each location can have many parts in it.

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.

I have a query showing the Inventory Items and their opening stock. I have a query showing the Transaction Log... do I need to create a union between these two to get what I am after? If so, how on earth do I do it? When I can, I will upload a version of my DB for you to see.

Many thanks in advance for all your help.

Chris

 

[plog]

No. You need to get rid of the OpeningQty field. Instead, just add a record to TransactionLog for it. That way you simply sum the quantity field in TransactionLog.

 

[arnelgp]

you need to create a Total Query on Transaction Log (qryTransLogTotal)

SELECT TransactionLog.PartID,
    TransactionLog.Origin,
    Sum(IIf([TransactionQty]>0,[TransactionQty],Null)) AS TotalReceipt,
    Sum(Nz(IIf([TransactionQty]<0,Abs([TransactionQty]),Null),0)) AS TotalIssuance
FROM TransactionLog
GROUP BY TransactionLog.PartID, TransactionLog.Origin;

then create Another query.
bring the inventory table and qryTransLogTotal.
join them on inventory.PartID = qryTransLogTotal.PartID and inventory.WHLocatID = qryTransLogTotal.Origin:

SELECT Inventory.WHLocatID,
    Inventory.PartID,
    Inventory.OpeningQty,
    qryTransLogTotal.TotalReceipt,
    qryTransLogTotal.TotalIssuance,
    [openingqty]+[totalReceipt]-[TotalIssuance] AS RemainingBalance
FROM Inventory
LEFT JOIN
qryTransLogTotal
    ON (Inventory.PartID = qryTransLogTotal.PartID)
    AND (Inventory.WHLocatID = qryTransLogTotal.Origin);

 

[ChrisC]

Hi both, thanks for your replies

ArnelGP - I have tried your codes (thank you) however unfortunately I'm not getting any results appearing on the screen. When I try to run either of the queries, I get another error - "Circular Reference caused by 'qryTransLogTotal'. Is the idea that when the query is run, the resulting datasheet view will show me all the parts and their "current" stock level?

I'm clearly missing something! Sorry for still not really understanding how this all works!!

thanks again!
Chris

 

[arnelgp]

maybe a sample db with your table structure
will help you.

your final query is qryFinal. see also the other query.

 

[ChrisC]

That is perfect, thank you. I will now go through it line by line to see why I couldn't get it to work

Thank you as always!
Chris