Solved

Retrieve corresponding field in group by query

Posted on 2014-01-13
3
123 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
3 Comments
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
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
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now