Quarterly Reports/Query Totals problem

**BDarden** · 12-26-2013, 06:14 PM

Hello,

I'm a beginner with Access, I have tried several approaches at this and apparently I am not even starting In the correct place, so here is what I have:

I need to pull a query/report that will show the following, grouped by Department

Department
Agent ID, Agent Name, January Average (this will be the average of Call Totals for all calls with a Audit Month of 1), February Average (this will be the average of Call Totals for all calls with a Audit Month of 2), March Average (this will be the average of Call Totals for all calls with a Audit Month of 3), Quarter 1 Average (This will be the average of Jan, Feb, Mar)

MasterDatabase - Copy.zip
I need this to pull for each agent that has any info,

Thank you so much in advance to anyone who can help me!

**millz** · 12-30-2013, 04:33 AM

Hi BDarden, create a new query using query design, switch to SQL view and paste this in. See if this is what you wanted.

SELECT [Audit Scores Database].AGENTID, [Audit Scores Database].AgentName, Sum(IIf([Audit Month]='1',[CALL TOTAL],0))/Count(IIf([Audit Month]='1',1)) AS [Jan Avg], Sum(IIf([Audit Month]='2',[CALL TOTAL],0))/Count(IIf([Audit Month]='2',1)) AS [Feb Avg], Sum(IIf([Audit Month]='3',[CALL TOTAL],0))/Count(IIf([Audit Month]='3',1)) AS [Mar Avg], Sum(IIf([Audit Month]='1' Or [Audit Month]='2' Or [Audit Month]='3',[CALL TOTAL],0))/Count(IIf([Audit Month]='1' Or [Audit Month]='2' Or [Audit Month]='3',1)) AS [Qtr 1 Avg]
FROM [Audit Scores Database]
GROUP BY [Audit Scores Database].AGENTID, [Audit Scores Database].AgentName