Help with Max and Min

[Shawn12]

Hey folks,

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

EmpID
EmployeeName
TotalHrsLogged

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.

 

[lespaul]

You mean this doesn't work?

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

replace qryName with the name of your query

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Shawn12]

No that returns everyone and their TotalHrsLogged. I only want the Highest and Lowest TotalHrsLogged and the 2 people that have them. I thought this was an easy one hehe.

 

[Shawn12]

I got it lespaul I just had to create a query w/ only the min and max vaules and then joined them to the original query based on those values to get the EmpID and Name..Thx again for the help.