Access Query to Display Max Value

New Contributor

Hi,

 

I'm trying to achieve the goal of displaying the data of my query to display a max value according to a main value. For example the query would look like this (with more entries and People);

 

Person | Date | RunTime | TotalShoeRunTime  

Dave    | Jan 1 | 3.00  |   15

Dave    | Jan 5 | 3.00 | 18

John     | Feb 4 | 5.00 | 20

John   |  Feb19 | 6.00 | 26

Nancy | Aug 23 | 7.00 | 30

Nancy | Aug 25 | 9.00 | 39

 

End Result I would like to see is the Three people and their max TotalShoeRunTime to be displayed. For example:

 

Dave  | Jan 5  |  3.00 |  15

John  | Feb 19 | 6.00 |  26

Nancy  | Aug 25  | 9.00 | 39

 

I look forward to any tips you would have and thank you for all the help!

 

Thanks!

4 Replies
Does your table not have a primary key?

Daniel,

Thank you for your reply. My table does have a primary key (I forgot to provide that in my question), the primary key is the RecordID# field. 

 

Joe

@WernJose 

 

Firstly, correct me if I'm wrong, but wouldn't the desired result be

 

Dave | Jan 5 | 3.00 | 18

John | Feb 19 | 6.00 | 26

Nancy | Aug 25 | 9.00 | 39

 

To do so, try an SQL Statement along the lines of

 

SELECT t1.*
FROM [YourTableName] t1
INNER JOIN (
SELECT Person, Max(TotalShoeRunTime) AS MaxOfTotalShoeRunTime
FROM [YourTableName]
GROUP BY Person
) t2 ON t1.Person = t2.Person AND t1.TotalShoeRunTime = t2.MaxOfTotalShoeRunTime

@Daniel_Pineault ,

You are correct with it being 18 not 15, my apologies for the mistake.
Great! I will try this out. I really appreciate your input and hope it will solve my problem!

Thanks again!

Joe