Count Issue (1 Viewer)

[MrDix]

Hi, I am using the code below to get the right count of Cases (records) for each agent.

SELECT tblCase.Agent, Count(*) AS pkCaseID
FROM tblCase
GROUP BY tblCase.Agent;

it works fine: I have 8 Cases (records)

My results are:

Eric = 2
Luis = 2
Carlos = 1
Tammy = 1
Nancy = 2

Now, My Issues is I wanted to group this monthly, so I can get a Monthly Report. Showing my agents total for each month.
Something like this is what I trying to achive:

JUNE 2005
Eric = 2
Luis = 1
Carlos = 1

JULY 2005
Luis = 1
Tammy = 1
Nancy = 2

Still showing the right amount of cases by seperated by month.
So I added the following code.

SELECT tblCase.Agent, Count(*) AS pkCaseID, tblCase.StartDate
FROM tblCase
GROUP BY tblCase.Agent, tblCase.StartDate;

But I get this instead:

June 2005
Eric = 1
Eric = 1
Luis = 1
Carlos = 1

July 2005
Luis = 1
Tammy = 1
Nancy = 1
Nancy = 1

Anyone can help my with this, point me to the right direction??
Thanks in advance.

 

[WayneRyan]

Mr. Dix,

I'd group them by a combination of the year and the date.
If you don't like the numeric months, you could add another
field to extract the "june" flavor of it, but this should
logically group them (using the year too).

SELECT tblCase.Agent, 
       Format(StartDate, "yyyymm") As SortDate,
       Count(*) AS pkCaseID
FROM tblCase
GROUP BY Agent, StartDate;

Wayne

 

[MrDix]

Wayne

Thanks, the format is fine but, still I am getting the same results as before

200506
Eric = 1
Eric = 1
Luis = 1
Carlos = 1

200507
Luis = 1
Tammy = 1
Nancy = 1
Nancy = 1

This is not what I am looking for - the format again is fine - but instead of showing
Eric = 1
Eric = 1
for the month of June

I want to show like this:
Eric = 2
for the month of Jun.

I not sure If is possible or ?

like that I can have a report showing each agent totals by months.

Thanks in advance..
MrDix....

 

[WayneRyan]

Mr. Dix,

SELECT Format(StartDate, "yyyymm") As SortDate,
       Agent,
       Count(*) AS pkCaseID
FROM tblCase
GROUP BY SortDate, Agent;

Wayne

 

[MrDix]

Thanks Wayne

I try it, but I get the following error:

You tried to execute a query that does not include specified expression 'Format([StartDate],"yyyymm")'as part of an aggregate function.

I am stuck here I can get the total amount grouped by Agent by I can't see to get it so I can have the report done Monthly???

MrDix

 

[WayneRyan]

Mr. Dix,

Can you post a sample db here?

Tools --> Database Utilities --> Compact/Repair
Then ZIP and attach.

Wayne

 

[MrDix]

Wayne,
I got it,
I created a new query using the following SQL code below. and it works for what I want it. So Now when I created my report I just hide [pkCustomerID]
but everything else works fine.

Many thanks, for your reply and tips.

MrDix...

SELECT DISTINCTROW Format$(tblCustomer.DateReceived,'mmmm yyyy') AS [DateReceived By Month], qryAgentsSummaryCustomers.fkAgentID, qryAgentsSummaryCustomers.pkCustomerID, Count(*) AS [Count Of tblAgent]
FROM (tblAgent INNER JOIN qryAgentsSummaryCustomers ON tblAgent.pkAgentID = qryAgentsSummaryCustomers.fkAgentID) INNER JOIN tblCustomer ON tblAgent.pkAgentID = tblCustomer.fkAgentID
GROUP BY Format$(tblCustomer.DateReceived,'mmmm yyyy'), qryAgentsSummaryCustomers.fkAgentID, qryAgentsSummaryCustomers.pkCustomerID, Year(tblCustomer.DateReceived)*12+DatePart('m',tblCustomer.DateReceived)-1;