Filter query to calculate sum of fields in date range (1 Viewer)

RyLane

Registered User.
Local time
Today, 04:45
Joined
Feb 4, 2014
Messages
60
Hello,

I'm looking for some guidance on this one.

I have a table, tblDailyCalls, that contains agent_name, date, calls_ answered, and talk_time. Ideally on a form, the user will select an agent, enter the date range in txtStartDate and txtEndDate and a report opens to show what the total amount of calls and talk time is for that date range.

All I've managed so far is doing a simple expression on the report itself to sum the fields I want. But my method returns every date in the range. I would like to only display the total.

I've been trying with Totals in the query and crosstab queries but am not familiar with them, so any suggestions on where to start would be appreciated.

Thanks,

Ryan
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:45
Joined
Feb 19, 2013
Messages
16,553
Post the sql you are using at the moment and we can suggest what you need to do to change it
 

RyLane

Registered User.
Local time
Today, 04:45
Joined
Feb 4, 2014
Messages
60
Hi CJ,

SELECT [Daily_Agent_Calls Original].Agent_Name, Sum([Daily_Agent_Calls Original].[Calls Answered]) AS [SumOfCalls Answered], Sum([Daily_Agent_Calls Original].Total_Talk_Time_min) AS SumOfTotal_Talk_Time_min, [Daily_Agent_Calls Original].Date
FROM [Daily_Agent_Calls Original]
GROUP BY [Daily_Agent_Calls Original].Agent_Name, [Daily_Agent_Calls Original].Date
HAVING ((([Daily_Agent_Calls Original].Date) Between [Forms]![Main]![txtStartDate] And [Forms]![Main]![txtEndDate]));

This gives me all the dates that fall in the range. I'm obviously not using the totals correctly because the sum is doing nothing
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:45
Joined
Feb 19, 2013
Messages
16,553
OK, what your query is doing is giving total by agent name and date,

If you just want total by agent name, in the query designer change the GROUP BY on the date column to WHERE (This will automatically untick the show option)

Then if you just want a total for all agents, remove the Agent Name column.

Note: Date is a reserved word (which means today) and can cause unexpected and difficult to resolve problems when used - strongly recommend you change it to something else - perhaps CallDate? At the very least, you should put it in square brackets
 

RyLane

Registered User.
Local time
Today, 04:45
Joined
Feb 4, 2014
Messages
60
That's perfect! Thanks

And yes, I just changed the Date field, I inherited this table and hadn't noticed yet.
 

Users who are viewing this thread

Top Bottom