Trouble with Top N by Group query

[AJGE]

Hey!

After much searching, and much reading... I am throwing in the towel and begging for help! haha...

I have a DB that is tracking race results... one table captures racer names, their racer #, their age category and skill category, contact info, etc. Another table captures a riders #, a race #, and their finish time....

From this I have a query that pulls a riders name, and their category info as well as their finish times for the races they have participated in. Using calculated fields, I then have a formula that calculates a given number of points that a rider has earned based on their finish time being compared to the fastest finisher in their category....

So far this is all just background info...

Here is where I am stuck...

Using this query that generates points, I have created a new query that pulls each riders Plate (racer #),First Name, Last Name, Points Earned, as well as a field called "singleorseries" which I have set the criteria to "Full Series"... this then only includes racers who are competing in the full season of race events. The query spills out roughly 1000 records that exist to date... What I now need to do is set this query to return only the best 10 POINTS EARNED values per each rider. I have grouped the query... and have read countless threads on the Top N by Group problem.... but I cannot for the life of me get it to apply and work.......

The basic solution I need to come to is to be able to eventually generate a report that will show each riders BEST 10 Finishes out of a possible 14 race events...

I have pasted the SQL view of the query below.....

If anyone can help walk me through this, I will be grateful!!

Thanks in advance,

AJ

SQL VIEW:
______________


SELECT [1 Riders and finishes Query].RiderPlate, [1 Riders and finishes Query].RiderFirst, [1 Riders and finishes Query].RiderLast, [1 Riders and finishes Query].AgeCategory, [1 Riders and finishes Query].RiderCategory, [1 Riders and finishes Query].[Points Earned], Riders.SingleorSeries
FROM [1 Riders and finishes Query] INNER JOIN Riders ON [1 Riders and finishes Query].RiderPlate = Riders.RiderPlate
GROUP BY [1 Riders and finishes Query].RiderPlate, [1 Riders and finishes Query].RiderFirst, [1 Riders and finishes Query].RiderLast, [1 Riders and finishes Query].AgeCategory, [1 Riders and finishes Query].RiderCategory, [1 Riders and finishes Query].[Points Earned], Riders.SingleorSeries
HAVING (((Riders.SingleorSeries)="Full Series"))
ORDER BY [1 Riders and finishes Query].RiderFirst, [1 Riders and finishes Query].RiderLast, [1 Riders and finishes Query].[Points Earned] DESC;

 

[AJGE]

Anyone out there? Any ideas?

 

[AJGE]

I have since simplified the query...
only tracking "RiderPlate" and "Points Earned".............

So I have the query grouped... and I am wanting to select the to 10 points values for each plate #.

I am trying the current SQL running a subquery... it is generating results... but not the right ones. Im swapping values around, but cant for the life of me determine why its being so problematic.


SELECT [1 PointsGen].RiderPlate, [1 PointsGen].[Points Earned]
FROM [1 PointsGen]
WHERE ((([1 PointsGen].[Riderplate]) In (Select Top 3 [Points Earned] from [1 PointsGen] where [Points Earned] = [1 pointsgen].[Points Earned] Order by [points earned] DESC)))
GROUP BY [1 PointsGen].RiderPlate, [1 PointsGen].[Points Earned];