GROUP BY Problem

[betheball]

I have a table wherein is recorded each employee's daily output, by date. I created a query that will tell me the highest volume daily ever for each employee.

SELECT Closures.IDRS, Max(Closures.[10Volume]) AS MaxOf10Volume
FROM Closures
GROUP BY IDRS;

However, I would like to also get the corresponding date for the daily high. The field is named InputDate. I know I can't add the field to the SELECT portion without also adding it to the GROUP BY clause, but that of course also breaks the query. Can I in one single query also get the InputDate?

 

[pdx_man]

As a single query ... well you could, in a way:

select Closures.TheDay
From Closures
INNER JOIN (SELECT SubQ.IDRS, Max(SubQ.[10Volume]) AS MaxOf10Volume
FROM Closures SubQ
GROUP BY SubQ.IDRS) ON Closures.IDRS = SubQ.IDRS AND Closures.10Volume = SubQ.MaxOf10Volume

Which is the same as if you created a sub-query, saved it and joined to it.

 

[betheball]

Thanks alot.