need help with Max function?

[Guest]

For my database, each employee can work in many departments and I want to
create a query that will select the department in which each employee works
most.


SELECT tble_employee.FName, tble_employee.SName, tble_hrs.Dept,
Count(tble_hrs.Dept) AS [Count], tble_employee.Work
FROM tble_employee INNER JOIN tble_hrs ON tble_employee.EmployeeID =
tble_hrs.EmployeeID
GROUP BY tble_employee.FName, tble_employee.SName, tble_hrs.Dept,
tble_employee.Work
HAVING (((tble_employee.Work)=True));

 

[Guest]

First change the New count field name to something different that is not a
Keyword in Access

SELECT tble_employee.FName, tble_employee.SName, tble_hrs.Dept,
Count(tble_hrs.Dept) AS NewCount, tble_employee.Work
FROM tble_employee INNER JOIN tble_hrs ON tble_employee.EmployeeID =
tble_hrs.EmployeeID
GROUP BY tble_employee.FName, tble_employee.SName, tble_hrs.Dept,
tble_employee.Work
HAVING (((tble_employee.Work)=True))

Then create a new query, based on the prvious query that will look like

Select Q1.* From QueryName As Q1 Where Q1.Dept In (Select Top 1 Q2.Dept From
QueryName As Q2 Where Q2.EmployeeID = Q1.EmployeeID Order By NewCount Desc)