Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Access query, find highest value in field

Posted on 2010-11-12
Medium Priority
521 Views
Hi. I have a table, tblMe, which has a number field called Mod (along with a few other fields). I need to return all records which have the highest Mod number.

In other words, if there are 2 records with a Mod of 4, 6 records with a mod of 8, and 4 records with a mod of 12, I only want to see those last 4 records (the ones with a mod of 12 or whatever the highest number is at that time).

Thanks much.
0
Question by:pkromer
• 3
• 2
• 2

LVL 44

Assisted Solution

GRayL earned 400 total points
ID: 34124676
SELECT a.Fld1, a.Fld2, a.Mod FROM myTable a WHERE a.Mod IN (SELECT Top 1 b.Mod FROM myTable b
WHERE b.Fld1 & a.Fld2 = a.Fld1 & a.Fld2 ORDER BY b.Mod DESC) ORDER BY a.Fld1, a.Fld2, a.Mod DESC;
0

LVL 44

Expert Comment

ID: 34124692
Confirm you do not want to see any other associated fields with the top Mod value(s)?  If that is the case:

SELECT Top 1 Mod FROM myTable ORDER BY Mod DESC;
0

LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 1600 total points
ID: 34124696

SELECT tblMe.ID, Max(tblMe.mod)
FROM tblMe
GROUP BY tblMe.ID
HAVING Max(tblMe.mod)=(select max([mod]) from tblMe)
0

LVL 44

Expert Comment

ID: 34124825
Thanks, but could you elaborate on how you decided to split the points, or for that matter, why you chose to close the question when I had a question outstanding?
0

Author Comment

ID: 34124938
GRayL,

I didn’t see that as a question, but rather an elaboration on your answer provided before it. I'm sorry, I certainly don't want to be awarding points inappropriately. It's just that capricorn1's answer got me there quickest.

I am about to open up another question related to this one because I just heard from the dept that needs this, they have additional criteria to add to the mix. So, if you want to try and help there I will certainly use your suggestions as much as possible. Thanks.
0

LVL 120

Expert Comment

ID: 34124976
pkromer,

if you want all the fields from the table, just use this query

select * from tblMe
where [mod]=(select max([mod]) from tblMe)
0

Author Comment

ID: 34124990
Thanks capricorn1, all good. As I said above, I am now opening another question based on this one. This one is complete, thanks again very much.
0

## Featured Post

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
###### Suggested Courses
Course of the Month11 days, 22 hours left to enroll