MS Access Help

Download the racing sense database. (free) It has the last 10 years or so of form. (Flat & NH)
It's a '97 database, but you can link the tables to another database in a later version. I don't think your idea of previous placings will get you too far. You could explore it in this database, by writing the form of your subset of horse's form to another table and using the racenumber field to find races which horses have previously met.

You could consider calculating some ratings of your own, or perhaps try to devise a system based upon the ssr ratings in the ssr database. There is a set of standard times in the SSR database and the other required info is in the tables described below.

http://www.racingsense.com/download.htm

The frace table contains the flat races
The frhorse contains the flat form
The hrace table contains the NH races
The hrhorse contains the NH form

However you approach it, this is a big project so don't go beating yourself up if you don't make it. 

You sound as though you're creating your queries through the Access query grid (sometimes called QBE, short for query by example) and although that will do a lot, it isn't capable of doing everything.  To write intermediate/advanced queries you need to learn SQL, which is a sentence that tells Access which records to retrieve.  Even if a query has been written using the QBE, you can still see the SQL in ACCESS by, from the query design, clicking on View | SQL View  There are masses of tutorials out there so just google.

Good Luck

This is how I'd design this.

TABLES
RACE (RaceID (auto), LocationID (int), Runners (Maybe leave this out?), RaceTime (int))
RACE_RESULT (Race_ResultID, RaceID, Place (int), HorseID, Completed (bool),IsPlace (bool))
HORSE (HorseID, HorseName)
LOCATION (LocationID, LocationName)


Based on this, to find a head to head between foo and bar you'd want the following query:

This involves subqueries. There may be a better way to do this, but because you effectively have to compare different lines, there's no way to do this in one "clean" query.

subq1:

SELECT RaceID, Place as [fooPlace], IsPlace as [fooIsPlace]
FROM Race
INNER JOIN Race_Result ON Race_Result.RaceID = Race.RaceID
INNER JOIN Horse ON Race_Result.HorseID = Horse.HorseID
WHERE Horse.HorseName = foo


subq2:
SELECT RaceID, Place as [barPlace], IsPlace as [barIsPlace]
FROM Race
INNER JOIN Race_Result ON Race_Result.RaceID = Race.RaceID
INNER JOIN Horse ON Race_Result.HorseID = Horse.HorseID
WHERE Horse.HorseName = bar


Now we can:

SELECT COUNT(subq1.RaceID)
FROM subq1 INNER JOIN subq2
WHERE [bar place] < [foo place] AND [barIsPlace] = TRUE


If you want to do that all in one query you can subquery all in one go as follows:


SELECT COUNT(subq1.RaceID)
FROM (SELECT RaceID, Place as [fooPlace], IsPlace as [fooIsPlace]
FROM Race
INNER JOIN Race_Result ON Race_Result.RaceID = Race.RaceID
INNER JOIN Horse ON Race_Result.HorseID = Horse.HorseID
WHERE Horse.HorseName = foo
)subq1 INNER JOIN (SELECT RaceID, Place as [barPlace], IsPlace as [barIsPlace]
FROM Race
INNER JOIN Race_Result ON Race_Result.RaceID = Race.RaceID
INNER JOIN Horse ON Race_Result.HorseID = Horse.HorseID
WHERE Horse.HorseName = bar
)subq2
WHERE [bar place] < [foo place] AND [barIsPlace] = TRUE

BTW if you're not comfortable using SQL you can still create and use subqueries through the query creator, just create the subqueries as queries then create a new query and you can query from those queries as if they were tables (just click add table then the query tab).


For the prediction you'll likely need to use VBA rather than just queries though.

Stow_Judge, Yeahyeah and ror thank you so much for your replies. I am still digesting what has been said but I will get back to you guys to let you know if I have been successful with it.

Thanks for taking the time to give me advice.