How to sum values in a query 1

[crisis2007]

I would like to have a query that shows me just the totals of each field for the month. I have the following query:

SELECT T_Receiving.ActivityDate, T_Receiving.ActivityTeam, T_Receiving.EmployeeNumber, T_StatNew.StatID, T_StatNew.RosterID, Sum(T_StatNew.Dispatched) AS SumOfDispatched, Sum(T_StatNew.[Self-Initiated]) AS [SumOfSelf-Initiated], Sum(T_Receiving.Reports) AS SumOfReports, Sum(T_Receiving.[Service Calls]) AS [SumOfService Calls], Sum(T_Receiving.Assists) AS SumOfAssists
FROM T_StatNew INNER JOIN T_Receiving ON T_StatNew.RosterID = T_Receiving.RosterID
GROUP BY T_Receiving.ActivityDate, T_Receiving.ActivityTeam, T_Receiving.EmployeeNumber, T_StatNew.StatID, T_StatNew.RosterID
HAVING (((T_Receiving.ActivityDate) Between #10/1/2011# And #10/31/2011#));


This query will show the sums of each field for each specific date. However I would like to only see the totals for the month - not for each specific date in the month. I just cannot find how to do this. Can someone point me in the right direction? Any help is greatly appreciated!

 

[dhookom]

You have a bunch of other fields you seem to be grouping by and we can only assume you still want these in your query. If so, try:

SELECT Format(T_Receiving.ActivityDate,"yyyymm") as ActivityYrMth, T_Receiving.ActivityTeam, 
T_Receiving.EmployeeNumber, T_StatNew.StatID, T_StatNew.RosterID, 
Sum(T_StatNew.Dispatched) AS SumOfDispatched, 
Sum(T_StatNew.[Self-Initiated]) AS [SumOfSelf-Initiated], 
Sum(T_Receiving.Reports) AS SumOfReports, 
Sum(T_Receiving.[Service Calls]) AS [SumOfService Calls], 
Sum(T_Receiving.Assists) AS SumOfAssists
FROM T_StatNew INNER JOIN T_Receiving ON T_StatNew.RosterID = T_Receiving.RosterID
WHERE T_Receiving.ActivityDate Between #10/1/2011# And #10/31/2011#
GROUP BY Format(T_Receiving.ActivityDate,"yyyymm"), T_Receiving.ActivityTeam, 
T_Receiving.EmployeeNumber, T_StatNew.StatID, T_StatNew.RosterID;

Duane
Hook'D on Access
MS Access MVP

 

[crisis2007]

Thank you! This is exactly what I needed. I did remove a couple of fields that were grouped by (EmployeeNumber, StatID, and RosterID) that were not needed after looking at what I was trying to do. Again, thank you very much!!!