sum of a column using group by: date criteria..

[Maven4Champ]

I have finally completed 7 different queries and 9 different reports and I just have this last one left.

I have already created a YTD TOTALS report which sums and averages all the data in my columns and returns a single value for each.

Now I am doing the MONTHLY TOTALS report which by putting in search criteria (i.e. From 06/01/2004 to 06/31/2004) will return all the totals in a single value for each - for that month...

Here is my code:

SELECT Sum(tblCentreVu.[Inbound ACD Calls]) AS [SumOfInbound ACD Calls], Avg([Avg Inbound ACD Time]/86400) AS [Avg In ACD Time], Avg([Avg ACW Time (Inbound ACD)]/86400) AS [Avg ACW Time (In ACD)], Sum(tblCentreVu.[Extn In Calls]) AS [SumOfExtn In Calls], Avg([Avg Extn In Time]/86400) AS [Avg_Extn In Time], Sum(tblCentreVu.[Extn Out Calls]) AS [SumOfExtn Out Calls], Avg([Avg Extn Out Time]/86400) AS [Avg_Extn Out Time], Sum(tblCentreVu.[External Extn Out Calls]) AS [SumOfExternal Extn Out Calls], Avg([Avg External Extn Out Time]/86400) AS [Avg_External Extn Out Time], Sum(tblCentreVu.[Trans Out]) AS [SumOfTrans Out]
FROM tblCentreVu
GROUP BY tblCentreVu.Date
HAVING (((tblCentreVu.Date)>=#6/1/2004# And (tblCentreVu.Date)<=#6/24/2004#));

The problem is, it is return all the totals for each day of the month. Now this isn't necesarrily bad because that might come in handy should I need a day-to-day/for month report to show (by each day) how we got to whatever total - but I am needing the report to reflect on value for each column (whether it be a sum or an avg) to reflect the totals for that date critera (i.e. per month basis)...

What do I need to change up in my query code to do this - or do I need to modify or add to my report to do this?

 

[bat17]

Change your Totals fild for date from "Group By" to "Where"

SELECT Sum(tblCentreVu.[Inbound ACD Calls]) AS [SumOfInbound ACD Calls], Avg([Avg Inbound ACD Time]/86400) AS [Avg In ACD Time], Avg([Avg ACW Time (Inbound ACD)]/86400) AS [Avg ACW Time (In ACD)], Sum(tblCentreVu.[Extn In Calls]) AS [SumOfExtn In Calls], Avg([Avg Extn In Time]/86400) AS [Avg_Extn In Time], Sum(tblCentreVu.[Extn Out Calls]) AS [SumOfExtn Out Calls], Avg([Avg Extn Out Time]/86400) AS [Avg_Extn Out Time], Sum(tblCentreVu.[External Extn Out Calls]) AS [SumOfExternal Extn Out Calls], Avg([Avg External Extn Out Time]/86400) AS [Avg_External Extn Out Time], Sum(tblCentreVu.[Trans Out]) AS [SumOfTrans Out]
FROM tblCentreVu
WHERE (((tblCentreVu.Date)>=#6/1/2004# And (tblCentreVu.Date)<=#6/24/2004#));

peter

 

[Maven4Champ]

Thanks alot. Worked like a charm. I see my error now.

I appreciate that.
Is there a way on the report to signify the date you are doing the report for? Like I noticed in the footer it will have the date you actually did the report on - but what about having text say 'The following report is for the month of:' and then in a box, have the corresponding month. Would I need to do this per just a list box, and create an individual table for it - or is there a way to handle that auotmaically in Access?

 

[bat17]

Depends on your set up, If you are passing the criteria from a form you could link the text boxes on the report to the form and pull in hte data directly. This will also save you from having to hard code the dates in your query each time.

Peter