have you got a sample database available? would make this easier.
If you are trying to do this in one query I don't think it's likely, you can do it with a combination of queries fairly easily though.
Assuming you already have a query that is totaling each employee by department you can create another query based on that query and select the employee ID and the MAX of the hours they worked. This would give you something to help you look up which department that MAX was in by linking this query back to the original.
so let's take a very simple table
PK |
ActivityDate |
EmployeeID |
Department |
HoursWorked |
1 |
1/1/2014 |
1 |
1 |
10 |
2 |
1/2/2014 |
1 |
1 |
8 |
3 |
1/3/2014 |
1 |
1 |
8 |
4 |
1/4/2014 |
1 |
1 |
6 |
5 |
1/5/2014 |
1 |
2 |
4 |
6 |
1/6/2014 |
1 |
2 |
10 |
7 |
1/7/2014 |
1 |
2 |
10 |
8 |
1/8/2014 |
1 |
2 |
10 |
Create this query:
Code:
SELECT tblTest.EmployeeID, tblTest.Department, Sum(tblTest.HoursWorked) AS TotHrs
FROM tblTest
WHERE (((tblTest.ActivityDate) Between #1/1/2014# And #1/31/2014#))
GROUP BY tblTest.EmployeeID, tblTest.Department;
Name it Qry_Base
Create this query:
Code:
SELECT Qry_Base.EmployeeID, Max(Qry_Base.TotHrs) AS MaxOfTotHrs
FROM Qry_Base
GROUP BY Qry_Base.EmployeeID;
Name it Qry_MajorDept
then create this query:
Code:
SELECT Qry_Base.EmployeeID, Sum(Qry_Base.TotHrs) AS SumOfTotHrs, Qry_Base_1.Department
FROM (Qry_Base LEFT JOIN Qry_MajorDept ON Qry_Base.EmployeeID = Qry_MajorDept.EmployeeID) LEFT JOIN Qry_Base AS Qry_Base_1 ON (Qry_MajorDept.MaxOfTotHrs = Qry_Base_1.TotHrs) AND (Qry_MajorDept.EmployeeID = Qry_Base_1.EmployeeID)
GROUP BY Qry_Base.EmployeeID, Qry_Base_1.Department;
It should sum the total hours by employee and give you the department number in which they had the most hours (handling departments where the same number of hours are present is another issue)