Query for MAX value (1 Viewer)

RJW

Registered User.
Local time
Today, 09:34
Joined
Sep 21, 2006
Messages
44
I need a query for records with the highest "DriveID"

Table
Name|Amount|DriveID

Bob|45.00|1
Sue|60.00|3
Ed|30.00|3
Jim|75.00|5
Barb|25.00|5
Tom|50.00|5

The highest value in that field will change as more records are added, so I was hoping to use something like the MAX function to query without knowing what the highest value is at any particular moment.

Thanks for time and help.
 

isladogs

MVP / VIP
Local time
Today, 13:34
Joined
Jan 14, 2017
Messages
18,186
Here's one approach.

Code:
SELECT TOP 1 Name, Amount, DriveID
FROM TableName
ORDER BY DriveID DESC;

Note that Name is a reserved word in Access and shouldn't be used for field names
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:34
Joined
Oct 29, 2018
Messages
21,358
Hi. Have you tried something like:
Code:
SELECT Max(DriveID) AS MaxDriveID FROM TableName


Edit: Oops, too slow, and I may have misunderstood the question. Sorry.
 

RJW

Registered User.
Local time
Today, 09:34
Joined
Sep 21, 2006
Messages
44
Thanks isladogs

First, I only used "Name" in the example. It's not the real name of the field.

Your solution worked great. But now on to Part 2.

The values in the "DriveID" field are always odd and always increase by two.

What I need is 4 separate queries for the 4 highest values in the "DriveID" field.

Can your solution be modified to accomplish that.


Thanks.


RJW
 

CindyKredo

New member
Local time
Today, 06:34
Joined
Nov 8, 2019
Messages
19
I would recommend using custom functions. Something along this line (although you may need to add code to handle a null return!)

Public Function GetSecondHighest() As Long

'return the second highest Drive ID from the table tblTest

Dim lHighest As Long
Dim lNextHighest As Long

lHighest = DMax("DriveID", "tblTest")
lNextHighest = DMax("DriveID", "tblTest", "DriveID < " & lHighest)

GetSecondHighest = lNextHighest


End Function

Similar code for the third and fourth, but calling the earlier function for the starting point!
 

isladogs

MVP / VIP
Local time
Today, 13:34
Joined
Jan 14, 2017
Messages
18,186
You could certainly do this using several custom functions as Cindy suggested but if you do want the other fields you will need to use the function in a query anyway.

Or you could use a solution based on the previous idea
1. SELECT TOP 1 as before (qryTop1)
2. Modify as SELECT TOP 2 and save as qryTop2. Now create an unmatched query for all in qryTop2 but not in qryTop1
3. Repeat for qryTop3 where not in qryTop2
4. Repeat for qryTop4 where not in qryTop3.

The idea is much the same as Cindy's suggestion but done using queries.
Choose whichever you find easiest/simplest

BTW its normally better to use real field and table names.
 

RJW

Registered User.
Local time
Today, 09:34
Joined
Sep 21, 2006
Messages
44
I'd rather keep it as just queries.

But when I change it from TOP 1 to TOP 2 or TOP 3 ...
I still only get the records with the highest DriveID value.

Am I missing something.

Here is the current SQL code:

SELECT TOP 1 Qy_All_FallDonations.*
FROM Qy_All_FallDonations
ORDER BY DriveID DESC;

SELECT TOP 2 Qy_All_FallDonations.*
FROM Qy_All_FallDonations
ORDER BY DriveID DESC;

SELECT TOP 3 Qy_All_FallDonations.*
FROM Qy_All_FallDonations
ORDER BY DriveID DESC;


Same results for all three.

RJW
 

isladogs

MVP / VIP
Local time
Today, 13:34
Joined
Jan 14, 2017
Messages
18,186
Sorry. That's because you have at least 3 records with the highest driveID.
If there are 5 records with that value SELECT TOP 6 would have those and one from the next highest value.

Anyway that approach won't work for you.

Instead you could try using one of the following methods
a) use subqueries as in this example Top N per group
b) use a rank order query then filter it. See https://www.access-programmers.co.uk/forums/showthread.php?t=297922
 

plog

Banishment Pending
Local time
Today, 08:34
Joined
May 11, 2011
Messages
11,613
Using the sample data you posted, please post what data you expect the query to return.
 

RJW

Registered User.
Local time
Today, 09:34
Joined
Sep 21, 2006
Messages
44
plog,

Answering using the sample data wouldn't explain it clearly. I posted a limited data set because I thought it was going to a simple solution. It turns out to be not so simple (from my perspective)

The actual table has 500 records.
18 records with DriveID 15
19 records with DriveID 13
28 records with DriveID 11
36 records with DriveID 9
27 records with DriveID 7

At the moment 15 is the highest value for the field DriveID

I need 4 separate queries for the highest 4 DriveID values.

Query A would have 18 records (15)
Query B would have 19 records (13)
Query C would have 28 records (11)
Query D would have 36 records (9)

When the next batch of new records get entered, then ...

Query A would have ?? records (17)
Query B would have 18 records (15)
Query C would have 19 records (13)
Query D would have 28 records (11)

I hope this explanation clears up my issue.

Thanks to everyone for helping

RJW
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:34
Joined
Oct 29, 2018
Messages
21,358
I need 4 separate queries for the highest 4 DriveID values.

Query A would have 18 records (15)
Query B would have 19 records (13)
Query C would have 28 records (11)
Query D would have 36 records (9)
I don't understand this part. Why do you "need" 4 separate queries? What would those individual queries can tell you that a single query with all the same information can't?
 

plog

Banishment Pending
Local time
Today, 08:34
Joined
May 11, 2011
Messages
11,613
I hope this explanation clears up my issue.

Nope. Explanations are not going to work, example data will. Please post sample data from your table. Don't summarize the data like you have in your prior posts, actually post complete data so we can see what you are starting with.

Include table and field names and enough records to cover all cases. No more words--use data.
 

RJW

Registered User.
Local time
Today, 09:34
Joined
Sep 21, 2006
Messages
44
The user wants a form with the past 4 drives listed as individual list boxes.

I'm sure there's a way to filter in the List box, but I prefer queries.

The solution provided by CindyKredo is working just fine.

Thanks CindyKredo.


RJW
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:34
Joined
Oct 29, 2018
Messages
21,358
The user wants a form with the past 4 drives listed as individual list boxes.

I'm sure there's a way to filter in the List box, but I prefer queries.

The solution provided by CindyKredo is working just fine.

Thanks CindyKredo.


RJW
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom