Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-08-2019, 09:43 AM   #1
RJW
Newly Registered User
 
Join Date: Sep 2006
Posts: 38
Thanks: 9
Thanked 0 Times in 0 Posts
RJW is on a distinguished road
Query for MAX value

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.

RJW is offline   Reply With Quote
Old 11-08-2019, 09:55 AM   #2
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,351
Thanks: 115
Thanked 3,108 Times in 2,826 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Query for MAX value

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
RJW (11-08-2019)
Old 11-08-2019, 09:57 AM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,628
Thanks: 58
Thanked 1,456 Times in 1,437 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Query for MAX value

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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-08-2019, 11:45 AM   #4
RJW
Newly Registered User
 
Join Date: Sep 2006
Posts: 38
Thanks: 9
Thanked 0 Times in 0 Posts
RJW is on a distinguished road
Re: Query for MAX value

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
RJW is offline   Reply With Quote
Old 11-08-2019, 12:03 PM   #5
CindyKredo
Newly Registered User
 
Join Date: Nov 2019
Location: Rochester NY
Posts: 5
Thanks: 1
Thanked 1 Time in 1 Post
CindyKredo is on a distinguished road
Re: Query for MAX value

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!
CindyKredo is offline   Reply With Quote
The Following User Says Thank You to CindyKredo For This Useful Post:
RJW (11-08-2019)
Old 11-08-2019, 12:20 PM   #6
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,351
Thanks: 115
Thanked 3,108 Times in 2,826 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Query for MAX value

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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
RJW (11-08-2019)
Old 11-08-2019, 01:45 PM   #7
RJW
Newly Registered User
 
Join Date: Sep 2006
Posts: 38
Thanks: 9
Thanked 0 Times in 0 Posts
RJW is on a distinguished road
Re: Query for MAX value

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

RJW is offline   Reply With Quote
Old 11-08-2019, 02:43 PM   #8
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,351
Thanks: 115
Thanked 3,108 Times in 2,826 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Query for MAX value

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 11-08-2019, 05:22 PM   #9
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,431
Thanks: 11
Thanked 2,286 Times in 2,238 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Query for MAX value

Using the sample data you posted, please post what data you expect the query to return.
plog is offline   Reply With Quote
Old 11-12-2019, 08:23 AM   #10
RJW
Newly Registered User
 
Join Date: Sep 2006
Posts: 38
Thanks: 9
Thanked 0 Times in 0 Posts
RJW is on a distinguished road
Re: Query for MAX value

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
RJW is offline   Reply With Quote
Old 11-12-2019, 08:29 AM   #11
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,628
Thanks: 58
Thanked 1,456 Times in 1,437 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Query for MAX value

Quote:
Originally Posted by RJW View Post
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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-12-2019, 08:46 AM   #12
RJW
Newly Registered User
 
Join Date: Sep 2006
Posts: 38
Thanks: 9
Thanked 0 Times in 0 Posts
RJW is on a distinguished road
Re: Query for MAX value

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
RJW is offline   Reply With Quote
Old 11-12-2019, 08:48 AM   #13
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,628
Thanks: 58
Thanked 1,456 Times in 1,437 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Query for MAX value

Quote:
Originally Posted by RJW View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
RJW (11-13-2019)
Old 11-12-2019, 08:42 AM   #14
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,431
Thanks: 11
Thanked 2,286 Times in 2,238 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Query for MAX value

Quote:
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.
plog is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 02:24 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World