Retrieve corresponding field in group by query

All, this should be so simple and yet I can't figure it out.  I have a table containing hourly volumes for a number of days (see excerpt below).

How do I write an access query that in addition to finding the maximum "totvol" for each day also returns the hour in which TotVol occurs?  So, from the table below I need to return two rows:
Day           Hour      TotVol
18-Jun-13      15      1563
19-Jun-13      5      1111

Retrieving the maximum TotVol value for each day is easy, but how do I get the associated hour?  

Here are the sample data:

Day           Hour      TotVol
18-Jun-13      15      1563
18-Jun-13      16      1481
18-Jun-13      17      1231
18-Jun-13      18      990
18-Jun-13      19      941
18-Jun-13      20      750
18-Jun-13       21      629
18-Jun-13      22      483
18-Jun-13      23      275
19-Jun-13      0      201
19-Jun-13      1      148
19-Jun-13      2      161
19-Jun-13      3      234
19-Jun-13      4      542
19-Jun-13      5      1111
mklikAsked:
Who is Participating?
 
FlysterConnect With a Mentor Commented:
You can do that with two queries. In the first query, add day and TotVol. In design view, select Totals. Set day to group and TotVol to Max. The sql look like this:

SELECT tblYourTable.Day, Max(tblYourTable.TotVol) AS MaxOfTotVol
FROM tblYourTable
GROUP BY tblYourTable.Day;

In a second query, add the first query and the table. Select the three fields you want from the table, Day, Hour, TotVol. Create a relation between TotVol in the table and MaxOfTotVol in the query. Here's the sgl:

SELECT tblYourTable.Day, tblYourTable.Hour, tblYourTable.TotVol
FROM tblYourTable INNER JOIN qryYourTable ON tblYourTable.TotVol=qryYourTable.MaxOfTotVol;

This should give you the result you want.

Flyster
0
 
hnasrCommented:
Try:
Select [Day], [Hour], TotVol
From tbl
Where [TotVol] = (Select Max(TotVol) From tbl as tbl2 Where tbl.[Day]=tbl2.[Day] And tbl.[Hour]=tbl2.[Hour])

Open in new window

0
 
mklikAuthor Commented:
Thanks for offering your time to help me out--I tried both suggestions.  

Unfortunately, hnsar's code simply replicates the original table (tblYourTable)--not sure why,

Flyster's query comes very close, but it sometimes returns two rows for the same day, with different values for TotVol and Hour.  Once I joined the table and query on the day fields as well, I got the result I was looking for.

Thanks for pointing me in the right direction!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.