I have slowly built my horse racing database for many years and am now at the stage where I need help with some queries.
Given a race with say 10 horses I want to find all races prior to the current race where at least 2 horses from current race have competed against one another. I want to look at form connections over approx last 5 years.
I am using MS-Access 2010, and MS-Excel 2010 (I know - they're old like me but still work). I use Python3 for downloading data from web.
My tables have following structure:
I'm using UK & AUS date format here.
varDateFrom = CDate("01/09/2012")
varDateTo = CDate("16/07/2017")
If possible, I would like to tweak this query to restrict the averages, and run counts, to only those races where 2 or more of the current field have raced each other in previous races.
I hope I have used correct formatting.
Hoping someone can point me in the right direction.
[Edit]
For some reason I cannot respond to contributors as I get this message. Anyway I will be away for a couple of days but will respond here by weekend
Regards
Given a race with say 10 horses I want to find all races prior to the current race where at least 2 horses from current race have competed against one another. I want to look at form connections over approx last 5 years.
I am using MS-Access 2010, and MS-Excel 2010 (I know - they're old like me but still work). I use Python3 for downloading data from web.
My tables have following structure:
- tblMeeting:
- ID_Meeting (PK),
- MtgDate,
- MtgVenue,
- MtgWeather,
- TrackCondition, etc ...
- tblRace:
- ID_Race (PK),
- ID_Meeting (FK),
- RaceNo,
- RaceDistance,
- RaceClass, etc ...
- tblField:
- ID_Field (PK),
- ID_Race (FK),
- IDHorse,
- Tote,
- Jockey,
- Trainer,
- WgtCarried,
- FinishPosn,
- Margin,
- TimeAdj,
- Rating, etc ...
I'm using UK & AUS date format here.
varDateFrom = CDate("01/09/2012")
varDateTo = CDate("16/07/2017")
SQL:
SELECT tblField.ID_Horse, Round(Avg([Rating]),10) AS AvgRtgH, Count(tblField.Rating) AS Runs
FROM (tblMeet INNER JOIN tblRace ON tblMeet.ID_Meeting = tblRace.ID_Meeting)
INNER JOIN tblField ON tblRace.ID_Race = tblField.ID_Race
WHERE (((tblMeet.MtgDate) Between #" & varDateFrom & "# And #" & varDateTo - 1 & "#))
GROUP BY tblField.ID_Horse
HAVING (((tblField.ID_Horse) In
(SELECT tblField.ID_Horse
FROM (tblMeet
INNER JOIN tblRace ON tblMeet.ID_Meeting = tblRace.ID_Meeting)
INNER JOIN tblField ON tblRace.ID_Race = tblField.ID_Race
WHERE (((tblMeet.MtgDate)=#" & varDateTo & "#) AND ((tblRace.RaceNo)=1) AND ((tblField.FStatus)='RUN')))
));
If possible, I would like to tweak this query to restrict the averages, and run counts, to only those races where 2 or more of the current field have raced each other in previous races.
I hope I have used correct formatting.
Hoping someone can point me in the right direction.
[Edit]
For some reason I cannot respond to contributors as I get this message. Anyway I will be away for a couple of days but will respond here by weekend
Regards
Attachments
Last edited: