searching on Max number in record set

[MICKI0220]

I am having a brain block in how to get the information out of a query. I have several records for an employee, but I want all the information of one record that has the Max number of days in the SumofHours field. If I group on everything and max out the SumOfHours field, I still get all the records....If I max the SumofHours field and the other fields that are different and group on the employee field, I get messed up data in the other max fields. My code is below..

SELECT qrySumSafety.EmployeeID, qrySumSafety.LastName, qrySumSafety.FirstName, qrySumSafety.MiddleInit, Sum(qrySumSafety.SumOfHours) AS SumOfSumOfHours, Max(qrySumSafety.ForemanLast) AS MaxOfForemanLast, Max(qrySumSafety.ForemanFirst) AS MaxOfForemanFirst, Max(qrySumSafety.ForemanID) AS MaxOfForemanID
FROM qrySumSafety
GROUP BY qrySumSafety.EmployeeID, qrySumSafety.LastName, qrySumSafety.FirstName, qrySumSafety.MiddleInit;


Any help is greatly appreciated
Micki

 

[PHV]

SELECT A.*
FROM qrySumSafety A INNER JOIN (
SELECT EmployeeID, MAX(SumOfHours) AS MaxDays FROM qrySumSafety GROUP BY EmployeeID
) M ON A.EmployeeID = M.EmployeeID AND A.SumOfHours = M.MaxDays

BTW, what is the SQL code of qrySumSafety ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MICKI0220]

The code for the sql query qrySumSafety is

SELECT Subsistence.EmployeeID, Subsistence.LastName, Subsistence.FirstName, Subsistence.MiddleInit, Sum(Subsistence.Hours) AS SumOfHours, Subsistence.ForemanLast, Subsistence.ForemanFirst, Subsistence.ForemanMiddle, Subsistence.ForemanID
FROM Subsistence
GROUP BY Subsistence.EmployeeID, Subsistence.LastName, Subsistence.FirstName, Subsistence.MiddleInit, Subsistence.ForemanLast, Subsistence.ForemanFirst, Subsistence.ForemanMiddle, Subsistence.ForemanID;

 

[PHV]

Did you try my suggested SQL ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MICKI0220]

working on it now.