Min and Max Help please...

[kalebson]

Hey folks,

What I have is a query that results in some general employee information including:

EmpID
EmployeeName
TotalHrsLogged

The original query looked like this:

SELECT EmployeeID, EmployeeName, Min(TotalHrsLogged) As LowestOcc, Max(TotalHrsLogged) As HighestOcc FROM qryName GROUP BY EmployeeID, EmployeeName

This however does not report ONLY 2 records, this reports everyone..

What im trying to do is report the EmpID, EmployeeName where its the MIN or MAX(TotalHrsLogged). I tried this:

HighestOcc: Max([TotalHrsLogged]) & " - " & [EmpID] & " " & [EmployeeName]

LowestOcc: Min([TotalHrsLogged]) & " - " & [EmpID] & " " & [EmployeeName]

These expressions do not cause an error however they do not display the correct results. I could not find a way to make it run at all without doing it as a concatenated field. Any help would be great. I do not have to have a concatenated field it can be 3 individual fields or whatever. I just need that data. Thanx in adv.

 

[neileg]

Use two queries. The first simply extracts the min and the max. Join this query with the original data using the TotalHrsLogged as the joining field. this will then return the associated employee IDs and names. This may be more than two records, of course, if you have ties.

 

[kalebson]

HAHA genius..thx that worked perfectly. Getting late in the week im about burnt 8).