Read this one(Union or Other?)

[Guest]

Sorry about the previous-accidently sent it-
I'm having a problem with a report, and after trying several of the
suggestions here I would be very grateful if someone could help me design the
query for it. It's on three tables, Categories, Products, and Inventory
Transactions. (standard Northwind fields here) I would like for the report to
show all of the products we stock from one category, with its current InStock
level, like this:
Category: Air Filter
ProductName SupplierName Part Number InStock
I have gotten everything else to work except the part where the Inventory
Transaction table comes in. The best I have done was a list of all of the
transactions for all of the products in that category, but not it's current
InStock level- Would this require a Union query or something else? Thanks so
much in advance,

 

[Michel Walsh]

Hi,


That generally requires a Total query. Group By on each "item", and SUM
the quantities ( + for credit, - for debit), add a criteria over the
SUM, asking for a SUM ( qty ) > 0. ( It is 0 if none is left, and
hopefully, never <0, or else, you over-sold something). Technically, the
simpler query could look like:


SELECT itemID, SUM(qty)
FORM transactions
GROUP BY itemID
HAVING SUM(qty) > 0


Save that query, and use it where you actually use the "transactions" table,
instead of the "transactions" table..


Hoping it may help and makes sense,
Vanderghast, Access MVP

 

[Guest]

Thank you for your help- I am going to try this - I'll let you know if I have
any problems!