Forum Discussion
Access Query to Display Max Value
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
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
