Solved

Using the max function in MS access query

Posted on 2008-09-30
3
742 Views
Last Modified: 2008-10-01
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
Comment
Question by:sannunzi
  • 2
3 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22609272
select [system number], max([coat])
from tableX
group by [system number]
0
 

Author Comment

by:sannunzi
ID: 22609452
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 22609567
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now