• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 806
  • Last Modified:

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.
0
sannunzi
Asked:
sannunzi
  • 2
1 Solution
 
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
 
Rey Obrero (Capricorn1)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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now