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


Joined
Jun 13, 2012
Messages
7
Reaction score
0
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!
:cheers:

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];
 
Ad

Advertisements

Joined
Jun 12, 2012
Messages
53
Reaction score
0
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;
 
Ad

Advertisements

Joined
Jun 13, 2012
Messages
7
Reaction score
0
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top