Record count by field

[Autoeng]

I can never get a query right (a lot of times I ask it to do something it can't as well).

I have a query to select records based on the following. What I want returned is a count of records for each of the ECN_Analyst. Is it possible?

Example
Analyst "A" worked ECN_Numbers 100, 200 & 300
Analyst "B" worked ECN_Number 400

Query results
Analyst "A" = 3
Analyst "B" = 1



SELECT tblECNMain.[ECN_Number], tblECNMain.[Do_Not_Process], tblECNMain.[ECN_Analyst], tblECNMain.[Release_Date]
FROM tblECNMain
WHERE (((tblECNMain.[ECN_Number]) Not Like "c*" And (tblECNMain.[ECN_Number]) Not Like "C*") AND ((tblECNMain.[Do_Not_Process]) Is Not Null) AND ((tblECNMain.[Release_Date]) Between [Forms]![frmDates]![Start_Date] And [Forms]![frmDates]![Stop_Date]))
ORDER BY tblECNMain.[ECN Analyst]
WITH OWNERACCESS OPTION;

Thanks,
Autoeng

 

[RichMorrison]

Make 2 queries.

Query 1 selects Analyst and ECN.

Query 2 uses Query 1 and Groups By Analyst. Make a field that is an Expression that is Count(*).

Voila.

RichM

 

[Autoeng]

Didn't work. Error is "Cannot have aggregate function in GROUP BY clause (Count(*)).

Here is new query.

SELECT qryanlyst.[ECN_Analyst], Count(*) AS Expr1
FROM qryanlyst
GROUP BY qryanlyst.[ECN_Analyst], Count(*)
WITH OWNERACCESS OPTION;

Autoeng

 

[RichMorrison]

Don't Group By Count(*)

RichM

 

[Autoeng]

Still not having any luck with this query. I am using the first query posted to get the information needed to count then using the below query to count by analyst. Any suggestions?

SELECT Count(*) AS Expr1, qryanlyst.[ECN_Number]
FROM qryanlyst
WITH OWNERACCESS OPTION;

 

[RichMorrison]

I think this should work:
<<
SELECT qryanlyst.[ECN_Analyst], Count(*) AS Expr1
FROM qryanlyst
GROUP BY qryanlyst.[ECN_Analyst]
WITH OWNERACCESS OPTION;
>>

That's just your first example with Expr1 removed from the Group By clause.

RichM

 

[Autoeng]

YES!! With a little tweak got exactly what I was looking for.

Thank you Rich Morrison!

Autoeng