Access Query Best 3

[douglasw32]

Have a query of a table with horses and speed figures for all races.

Want to pull just the 3 best speed figures along with the horse and other data about the horse

How do you limit a query to just the top 3 ?

Using "RETURN" I can limit it to 3 but it shows the 3 best from the whole recod set, I need the top 3 per race

[richrosa]

Have a query of a table with horses and speed figures for all races.

Want to pull just the 3 best speed figures along with the horse and other data about the horse

How do you limit a query to just the top 3 ?

Using "RETURN" I can limit it to 3 but it shows the 3 best from the whole recod set, I need the top 3 per race

select * from horses where race = **** order by speed_rating asc limit 3;

[Tom Barrister]

You need to have a ranking function for each race. If you don't have that in your database, you'll need to create one in VBA (the programming language for Access). If you don't know VBA (and I keep telling myself that I'm going to learn it eventuallY), there are several people here who would know how to do this, and if you can't find one here, there are several over on the HTR forums. One will probably create the applet for you for a reasonable price (or possibly free).

Speed figures are beaten to death. I haven't found anything that relies mainly on commercial speed figures that can crack 0.90 ROI. If you have proprietary figures, it might be a different ballgame.

[douglasw32]

No "LIMIT" in access, TOP works but does not break it by race? =(

[cj]

No "LIMIT" in access, TOP works but does not break it by race? =(

This is how I do it...dirt is the "speed figure". You should be able to get where you want to go from this...

SELECT Horses1.Track, Horses1.Date, Horses1.Race, Horses1.Dirt, (Select Count(*) from Horses Where (([Dirt] > [Horses1].[Dirt]) And ([Track] = [Horses1].[Track]) And ([Date] = [Horses1].[Date]) And ([Race] = [Horses1].[Race]) ))+1 AS Dirt_Rank
FROM Horses AS Horses1
GROUP BY Horses1.Track, Horses1.Date, Horses1.Race, Horses1.Dirt
HAVING ((((Select Count(*) from Horses Where (([Dirt] > [Horses1].[Dirt]) And ([Track] = [Horses1].[Track]) And ([Date] = [Horses1].[Date]) And ([Race] = [Horses1].[Race]) ))+1)<4))
ORDER BY Horses1.Track, Horses1.Date, Horses1.Race, Horses1.Dirt DESC;



This gives the following results, first several rows:

Track Date Race Dirt Dirt_Rank
ALB 20070810 1 76 1
ALB 20070810 1 72 2
ALB 20070810 1 63 3
ALB 20070810 2 77 1
ALB 20070810 2 69 2
ALB 20070810 2 68 3
ALB 20070810 3 40 1
ALB 20070810 3 37 2
ALB 20070810 3 32 3
ALB 20070810 4 46 1
ALB 20070810 4 42 2
ALB 20070810 4 38 3
ALB 20070810 5 59 1
ALB 20070810 5 57 2
ALB 20070810 5 56 3
ALB 20070811 5 76 1
ALB 20070811 5 69 2
ALB 20070811 6 58 1
ALB 20070811 6 56 2
ALB 20070811 6 50 3
ALB 20070811 7 50 1
ALB 20070811 7 45 2
ALB 20070811 7 41 3
ALB 20070811 8 84 1
ALB 20070811 8 75 3
ALB 20070811 9 88 1
ALB 20070811 9 81 2
ALB 20070811 9 79 3
ALB 20070811 10 60 1
ALB 20070811 10 57 2
ALB 20070811 10 55 3
ALB 20070812 5 65 1
ALB 20070812 5 64 2
ALB 20070812 5 61 3
ALB 20070812 6 55 1
ALB 20070812 6 54 2
ALB 20070812 6 50 3
ALB 20070812 7 70 1
ALB 20070812 7 53 2
ALB 20070812 7 45 3
ALB 20070812 8 38 1
ALB 20070812 8 31 2
ALB 20070812 8 29 3
ALB 20070812 9 78 1
ALB 20070812 9 77 2
ALB 20070812 9 73 3
ALB 20070812 10 87 1
ALB 20070812 10 78 3

You will note some races there are only 1 and 3, which means there was a tie for 1. Other races there are only 1 and 2, which means there was a tie for 2nd. By adding in the program number field, I was able to display the ties as seen below:

ALB 20070810 1 76 1 1
ALB 20070810 1 72 2 2
ALB 20070810 1 63 5 3
ALB 20070810 2 77 11 1
ALB 20070810 2 69 4 2
ALB 20070810 2 68 8 3
ALB 20070810 3 40 8 1
ALB 20070810 3 37 11 2
ALB 20070810 3 32 7 3
ALB 20070810 4 46 1 1
ALB 20070810 4 42 5 2
ALB 20070810 4 38 10 3
ALB 20070810 5 59 2 1
ALB 20070810 5 57 1 2
ALB 20070810 5 56 5 3
ALB 20070811 5 76 4 1
ALB 20070811 5 69 5 2
ALB 20070811 5 69 6 2
ALB 20070811 6 58 6 1
ALB 20070811 6 56 9 2
ALB 20070811 6 50 2 3
ALB 20070811 7 50 4 1
ALB 20070811 7 45 6 2
ALB 20070811 7 41 1 3
ALB 20070811 8 84 10 1
ALB 20070811 8 84 7 1
ALB 20070811 8 75 2 3
ALB 20070811 9 88 6 1
ALB 20070811 9 81 2 2
ALB 20070811 9 79 5 3
ALB 20070811 10 60 2 1
ALB 20070811 10 57 6 2
ALB 20070811 10 55 1 3
ALB 20070811 10 55 4 3
ALB 20070812 5 65 6 1
ALB 20070812 5 64 1 2
ALB 20070812 5 61 4 3
ALB 20070812 6 55 6 1
ALB 20070812 6 54 3 2
ALB 20070812 6 50 5 3
ALB 20070812 7 70 2 1
ALB 20070812 7 53 4 2
ALB 20070812 7 45 5 3
ALB 20070812 8 38 3 1
ALB 20070812 8 31 7 2
ALB 20070812 8 29 2 3
ALB 20070812 9 78 9 1
ALB 20070812 9 77 6 2
ALB 20070812 9 73 3 3
ALB 20070812 9 73 4 3
ALB 20070812 10 87 3 1
ALB 20070812 10 87 6 1
ALB 20070812 10 78 4 3

[douglasw32]

CJ- I can not thank you enough for sharing this information, it is very hard to explain to an access forum what I am trying to do.

[cj]

No problem...I'm not an expert, but I'm an expert at learning via Google. :)

[douglasw32]

Exactly =) , Thanks again.