Solved

Retrieve corresponding field in group by query

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

This article will show you how to use shortcut menus in the Access run-time environment.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

749 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