troubleshooting Question

Retrieve corresponding field in group by query

Avatar of mklik
mklik asked on
Microsoft OfficeMicrosoft Applications
3 Comments1 Solution148 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Flyster

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros