Query to find best of most recent 3 speed ratings by date

[Peter Webb]

Hi,
I calculate speed ratings for horse racing in the UK. My database now
contains over 100,000 ratings. I am a bit of a novice at Access and have
been exporting ratings from the database earned in the last few months and
doing a lot of manual sorting in excel to reduce the no. of ratings for each
race to a sensible number. I would like to be able to select the best of the
last 3 ratings for each horse using queries/macros. I have tables for
horses, speed ratings, declarations for the next day, etc.
I would very much appreciate some assistance with this.

I will happily send anyone a sample database. (simplified)

(I tried to attach it to a message, but the message dissapeared!)

Pete.

 

[John Viescas]

Peter-

Most folks won't touch any binary file posted in a newsgroup. Please post a
brief description of your table layout and a more concise description of
what you want to achieve - perhaps with a few sample rows with the relevant
columns and the result you expect. I suspect you'll be able to use a Top
Values query, but I can't provide a more specific answer until I know the
table layout.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

 

[Peter Webb]

The important columns are

Speed ratings table

RACENUMBER,RACEDATE, NAME,WEIGHT, SpeedW,COURSE, DISTANCE.

(Name is horse name, races are numbered numerically from the start of the
season,
SpeedW is the weight adjusted speed rating)

Declarations (runners)

DATE, TIME,NAME,WEIGHT,DISTANCE

(Name is horse name, index ties

The speed ratings table contains an entry for every individual horse's runs,
including a racenumber, the race date, weight carried etc and the speed
rating for every one of each individual horses runs. I wish to match the
horse declarations (today's runners) with the with each horses individual
rating and select the best rating of the most 3 recent runs for each
individual horse. (many horses may have 10+ individual ratings in the
database, stored by racenumber and race date)
The final output is essesntially the race details with one speed rating for
each horse.
The attachment is an Access 2000 database zipped, containing example data,
which I have described above.

I Hope this is clearer.

Peter.

 

[John Viescas]

Peter-

The basic query is:

SELECT Declarations.*, SpeedRatings.*
FROM Declarations
INNER JOIN SpeedRatings
ON Declarations.[Name] = SpeedRatings.[Name]
WHERE SpeedRatings.RaceDate IN
(SELECT TOP 3 RaceDate
FROM SpeedRatings As S2
WHERE S2.[Name] = Declarations.[Name]
ORDER BY RaceDate DESC)

You can feed this into a Totals query to find out the Max rating.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411