Access Looking for assistance with ACCESS "Best 10 of Grouped Query"

[AJGE]

Hey,

I have a grouped query that is returning multiple records per grouping.

I have read up on and tried the proposed solutions that others have posted... to no avail.

I am trying to have the query return only the highest 10 records per group.

For simplicity sake, the groups are peoples names, and the 10 highest records are going to be each persons best finish result in a race.

The sorting and grouping is complete...

If anyone out there thinks they can help me straighten this one out... id be grateful!


Post or PM if you need any additional info.
I have posted the sql view for the query below.

This is from a backed up version of the DB, so table names are a mess... this much I am already aware of. Once I solve this one, the whole DB is getting an clean up and re-write.

The RiderPlate, RiderFirst, RiderLast, RiderSex, AgeCategory, and RiderCategory are what creates the unique group.

What I need to find is the highest 10 values within the "PointsEarned" field.

Thanks in advance!!!

AJ
______________________________________


SELECT [1 Riders and finishes].RiderPlate, [1 Riders and finishes].RiderFirst, [1 Riders and finishes].RiderLast, [1 Riders and finishes].RiderSex, [1 Riders and finishes].AgeCategory, Riders.Age, Riders.RiderCategory, [1 Riders and finishes].[Points Earned]
FROM [1 Riders and finishes] INNER JOIN Riders ON [1 Riders and finishes].RiderPlate = Riders.RiderPlate
GROUP BY [1 Riders and finishes].RiderPlate, [1 Riders and finishes].RiderFirst, [1 Riders and finishes].RiderLast, [1 Riders and finishes].RiderSex, [1 Riders and finishes].AgeCategory, Riders.Age, Riders.RiderCategory, [1 Riders and finishes].[Points Earned];

 

[goodfellow]

Maybe you should create separate queries for each group and select your groups not by GROUP BY clause but by WHERE statement. For example (one table for simplicity):

SELECT TOP 10 RiderPlate, RiderFirst, RiderLast, RiderSex, AgeCategory, Age, RiderCategory, PointsEarned
FROM Results
WHERE RiderSex = 'female' AND Age > 16 AND Age < 25
ORDER BY PointsEarned DESC;

 

[AJGE]

I have considered going that route.... but at the end of the day, I will still end up with many results for each racer... and still will need to select each riders best 10 results.

The criteria setting using "N of group" seems to be the way to go... but I cannot sort out the proper command. I was wondering if anyone reading this may have already tackled this problem....

Let me know!