Query for MAX value

[RJW]

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]

Here's one approach.

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]

Hi. Have you tried something like:

SELECT Max(DriveID) AS MaxDriveID FROM TableName

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

[RJW]

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]

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]

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]

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]

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...d.php?t=297922

[plog]

Using the sample data you posted, please post what data you expect the query to return.

[RJW]

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]

Originally Posted by RJW

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]

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]

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]

Originally Posted by RJW

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.