I have a table that contains like the following:

EmployeeName, Reason

I need to count the occurences of each reason for each employee and select the reason that occurs the maximum number of times.
I thought about doing a nested Select query, but I can only get this to return the employee with their reason that occured the maximum number of times

ie
Joe Bloggs, Credit Authorised

I can't seem to get the query to return each employee with their reason that occurred for them the maximum number of times.

ie
A N Other, Enquiry
Joe Bloggs, Credit Authorised
John Doe, Escalated Call
S O Else, Credit Authorised

Thanks in advance
Ben

Create a query of Counts and name it qryEmployeeReasonCount

SELECT EmployeeName, Count(Reason) AS CountReason
FROM tblEmployeeReason
GROUP BY [EmployeeName & ' ' & Reason, EmployeeName
ORDER BY EmployeeName & ' ' & Reason;

Then take the max from that query by writing a new query referencing that query:

SELECT EmployeeName, Max(CountReason) AS MaxCountReason
FROM qryEmployeeReasonCount
GROUP BY EmployeeName;

This isn't finished as I didn't take the time to pull the reason but should get you pointed in one workable dirction.

Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]