Grouping records

[stewartorris]

Hello,


I have yet another question! This involves loops again. Forgive me, I'm still learning.


I am trying to retreive and sort through a tbl of records for inventory transactions. I want to get all the records from the tbl that relate to a particular Purchase Order (poID). Then I want to group all of the records together that are transactions for the same product (productID). Within those like records, I need to add together the unitsOrdered fields for a total units ordered amount to display. This table is listed below.

tbl: trans_inventory
tbl fields:
transID
transDate
productID
poID
invoiceID
unitsOrdered
unitsReceived



Here's my problem, I can't seem to get started with getting the records and grouping them. I keep getting aggregate function errors because I am trying something like this:

SELECTpoID, productID, unitsReceived, unitsOrdered FROM trans_inventory WHERE poID = "& poNumber &" GROUP BY productID

(poNumber is a variable defined earlier.)

Any thoughts? Any suggestions, as always, will be greatly appreciated. Thank you!

JSO

[Angelika]

Note: Group by can only be used with an aggregate function - Avg(), Count(), Count (Distinct), Max(), Min(), or Sum().

[stewartorris]

I tried setting up the select statement like this and still got an error:


SELECT poID, Count(unitsReceived), productID, ... FROM trans_inventory WHERE poID = "& poNumber &" GROUP BY productID

The error was regarding the poID since it was not used in an aggregate function.


I'm just a little confused as to how to organize all of this.


Thanks.

[Angelika]

Why you do not try this?

SELECT Count(poID) as poID, unitsReceived, productID, ... FROM trans_inventory WHERE poID = "& poNumber &" GROUP BY productID

[stewartorris]

I suppose I could but I am trying to get a total on the unitsReceived field. My goal is to get a list of all the products relating to a particular order (poID) and the total unitsReceived for each one of those products (productID).

Thanks for your help.

[Angelika]

Do you try use Sum()?
SELECT poID, Sum(unitsReceived), productID, ... FROM trans_inventory WHERE poID = "& poNumber &" GROUP BY productID

[stewartorris]

Yeah, I think it has something to do with the WHERE poID = "& poNumber &".

Here's the actual error message:

You tried to execute a query that does not include the specified expression 'poID' as part of an aggregate function.


Thanks.

[Angelika]

How could you?