Access Query to Display Max Value

%3CLINGO-SUB%20id%3D%22lingo-sub-2089034%22%20slang%3D%22en-US%22%3EAccess%20Query%20to%20Display%20Max%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2089034%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20achieve%20the%20goal%20of%20displaying%20the%20data%20of%20my%20query%20to%20display%20a%20max%20value%20according%20to%20a%20main%20value.%20For%20example%20the%20query%20would%20look%20like%20this%20(with%20more%20entries%20and%20People)%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3EPerson%20%7C%20Date%20%7C%20RunTime%20%7C%20TotalShoeRunTime%26nbsp%3B%26nbsp%3B%3C%2FU%3E%3C%2FP%3E%3CP%3EDave%26nbsp%3B%20%26nbsp%3B%20%7C%20Jan%201%20%7C%203.00%26nbsp%3B%20%7C%26nbsp%3B%20%26nbsp%3B15%3C%2FP%3E%3CP%3EDave%26nbsp%3B%20%26nbsp%3B%20%7C%20Jan%205%20%7C%203.00%20%7C%2018%3C%2FP%3E%3CP%3EJohn%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%7C%20Feb%204%20%7C%205.00%20%7C%2020%3C%2FP%3E%3CP%3EJohn%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20Feb19%20%7C%206.00%20%7C%2026%3C%2FP%3E%3CP%3ENancy%20%7C%20Aug%2023%20%7C%207.00%20%7C%2030%3C%2FP%3E%3CP%3ENancy%20%7C%20Aug%2025%20%7C%209.00%20%7C%2039%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnd%20Result%20I%20would%20like%20to%20see%20is%20the%20Three%20people%20and%20their%20max%20TotalShoeRunTime%20to%20be%20displayed.%20For%20example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDave%26nbsp%3B%20%7C%20Jan%205%26nbsp%3B%20%7C%26nbsp%3B%203.00%20%7C%26nbsp%3B%2015%3C%2FP%3E%3CP%3EJohn%26nbsp%3B%20%7C%20Feb%2019%20%7C%206.00%20%7C%26nbsp%3B%2026%3C%2FP%3E%3CP%3ENancy%26nbsp%3B%20%7C%20Aug%2025%26nbsp%3B%20%7C%209.00%20%7C%2039%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20look%20forward%20to%20any%20tips%20you%20would%20have%20and%20thank%20you%20for%20all%20the%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2089034%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EQuery%20Max%20Values%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2089422%22%20slang%3D%22en-US%22%3ERe%3A%20Access%20Query%20to%20Display%20Max%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2089422%22%20slang%3D%22en-US%22%3EDoes%20your%20table%20not%20have%20a%20primary%20key%3F%3C%2FLINGO-BODY%3E
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