Using the max function in MS access query

I have an access query that shows system number and coat.  If I have a system number of 1 and a coat of 1 and a system number of 1 and a coat of 2, how do I write that in my query to only show the system number 1 with the max coat target?  I can also have a system number 2 or 3 or 4 with multiple coats, and I only want to see the max of those also.
sannunziAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
sannunzi,
you just mentioned [system number] and coat.  now, you are adding another field.

what are the values in the field die?

select [system number], max([coat]),[die]
from tableX
group by [system number],[die]
0
 
Rey Obrero (Capricorn1)Commented:
select [system number], max([coat])
from tableX
group by [system number]
0
 
sannunziAuthor Commented:
That doesn't work.  I also have a field that is called die that should be displayed also along with a coat date.  The coat date is linked to the max coat, but I can't get that to work either.  I just want the query result to show the system number with the max coat and the die.  I could have rows with multiple records that have system number 1 and die 25 for example but have coat 1,2,3
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.