Solved

Retrieve corresponding field in group by query

Posted on 2014-01-13
3
129 Views
Last Modified: 2014-01-14
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
0
Comment
Question by:mklik
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 39778409
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
 
LVL 22

Accepted Solution

by:
Flyster earned 500 total points
ID: 39778432
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
 

Author Closing Comment

by:mklik
ID: 39780258
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question