Solved

Retrieve corresponding field in group by query

Posted on 2014-01-13
3
130 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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 …
Suggested Courses

636 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