Access query, find highest value in field

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.
pkromerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:

SELECT tblMe.ID, Max(tblMe.mod)
FROM tblMe
GROUP BY tblMe.ID
HAVING Max(tblMe.mod)=(select max([mod]) from tblMe)
0
 
GRayLConnect With a Mentor Commented:
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
 
GRayLCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
GRayLCommented:
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
 
pkromerAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
pkromerAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.