asked on

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.

SOLUTION

GRayL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GRayL

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;

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GRayL

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?

pkromer

ASKER

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.

Rey Obrero (Capricorn1)

pkromer,

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

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

pkromer

ASKER

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.