Query for sum of values by date

[Mattbro]

Hello all,


I have two tables;
A table showing the item details-Table FinalWork; Receive Date, ID (Primary Key), NoBricks, NoCubes, DateBooked, DateOut


A table showing how may whole time equivalent (WTE) staff are in post on that day. Table Hours; Date (primary Key), WTE


I have a relationship between Hours.Date and Finalwork.ReceiveDate.


I am trying to develop a query and ultimately a report that will return the sum of NoBricks, the sum of NoCubes and number for WTE for each day (ReceiveDate).

So far I am struggling-the SQL reads;

SELECT Sum(FinalWork.[Total NoBricks]) AS [SumOfTotal NoBricks], Sum(FinalWork.[Total NoCubes]) AS [SumOfTotal NoCubes], Hours.WTE, FinalWork.[Receive Date]
FROM FinalWork INNER JOIN Hours ON FinalWork.[Receive Date] = Hours.Date
GROUP BY Hours.WTE, FinalWork.[Receive Date], FinalWork.[DateOut], FinalWork.[DateBooked], FinalWork.[ID];

I am sure my problem is in the GROUP BY section, but I have been looking everywhere- at the group function in reports, and DCount, amongst other things, but I am not getting anywhere. If anyone can help, I would be grateful.
Regards,
Mattbro

[offie]

Why are you grouping by fields you haven't selected?

[Mattbro]

....because I keep getting 'does not include specified expression' ' as part of the aggregate function' warnings. I think you are right though-having removed the articles that cause the warning from the query, I am now getting the data I want.
Thanks,
Mattbro