SUM in grouped query?

[Guest]

Hi, I am having a problem. I havw three tables:
Subinventories - Names and IDs of locations
ccfile - All my widgets, numbers, quantities etc.
[Check in Items] - Items I have scanned
What I am trying to do is (Pseudocode SQL, sorry if it's not clear):
Go through [Check In Items]. Group by SubInv and bring in
Subinventories.Name where Subinventories.SubInv = [Check In Items].SubInv.
Then, Group by ItemNumber. For each ItemNumber I have nested in this report,
I want to have a field named ItemCount that is the SUM of ccfile.qty where
ccfile.itemnumber = [check in items].itemnumber and [ccfile].subinv = [check
in items].subinv

I have everything working except the SUM. I have been tooling around trying
to fix it, changed the joins, etc. and I am banging my head against the wall.
My current query is:

SELECT [Check In Items].id, [Check In Items].subinv, [Check In
Items].itemnumber, [Check In Items].lotcode, [Check In Items].matchedlot,
Ccfile.ItemDescription, Sum([ccfile].[Qty]) AS ItemCount, Subinventories.Name
FROM Subinventories INNER JOIN (Ccfile INNER JOIN [Check In Items] ON
Ccfile.ItemNumber = [Check In Items].itemnumber) ON
Subinventories.Subinventory = Ccfile.SubInv GROUP BY [ccfile].itemnumber;

I get an error:
You tried to execute a query that does not include the specified expression
'id' as part of an aggregate function.

Any ideas what I can do? Thanks for any help in advance.

 

[Roger Carlson]

In an aggregate query, every field in the field list must either be
aggregated (i.e.Sum([ccfile].[Qty]) AS ItemCount) or be in the GROUP BY
clause. So you need to put all your fields except ccfile in the GROUP BY
clause.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L