Top 3 times for each distance

[CraigBFG]

Hi all

I've tried to follow the MS example in http://support.microsoft.com/kb/210039 which works for just 2 tables, but I have more!

tblDistances / tblRaces / tblResults.
Each race has a single distance - there are many races with the same distances - each race has many results.

So the objective is to find the top 3 results for each distance, and show the race name as a consequence. My query so far is as below which just returns the overall top 3 and not per group.

As always - help & guidance is always appreciated.


SELECT tblRaces.RaceName, tblRaces.DistID, tblresults.ResTime, tblresults.ResName
FROM tblDistance INNER JOIN (tblRaces INNER JOIN tblresults ON tblRaces.RaceID = tblresults.RaceID) ON tblDistance.DistID = tblRaces.DistID
WHERE (((tblresults.ResTime) In (Select Top 3 [ResTime] From tblResults Where [tblRaces.DistID]=[tblDistance].[DistID] Order By [ResTime] Asc)))
ORDER BY tblRaces.DistID, tblresults.ResTime;