?
Solved

Using the max function in MS access query

Posted on 2008-09-30
3
Medium Priority
?
794 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1500 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…
Suggested Courses

777 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