1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
  2. There seems to be a problem with some alerts not being emailed to members. I have told the hosts and they are investigating.
    Dismiss Notice
Welcome to the UK Betting Forum. Please consider registering, it is free!

Using MS Access with Raceform Interactive

Discussion in 'Databases' started by davevart, May 9, 2015.

  1. davevart

    davevart Foal

    Joined:
    Apr 30, 2015
    Messages:
    5
    Likes Received:
    0
    Could anyone help me with using MS Access over RI data please. I wish to query a horses previous 3 runs historically.....eg. so when it ran at Chelt in the 2013 Festival what were it's previous 3 runs RF rating?
     
  2. leftinthestalls

    leftinthestalls Yearling

    Joined:
    Feb 2, 2013
    Messages:
    123
    Likes Received:
    159
    something like this, assuming a table named racedata

    SELECT TOP 3 racedata.HorseID, racedata.RPR, racedata.Racedate
    FROM racedata
    WHERE (((racedata.HorseID)=123456) AND ((racedata.Racedate)<#3/11/2015#))
    ORDER BY racedata.Racedate DESC;
    the key to this is sorting by the race date in descending order.
     
  3. ArkRoyal

    ArkRoyal Administrator

    Joined:
    Dec 28, 2012
    Messages:
    12,615
    Likes Received:
    12,675
    @davevart have you linked the RI tables to Access yet, that's the first step.
     
  4. davevart

    davevart Foal

    Joined:
    Apr 30, 2015
    Messages:
    5
    Likes Received:
    0
    I've exported from RI to races and runners txt files.....is it better using ODBC or something of that ilk. Not great on these things so may need some handholding! Ta for quick reply
     
  5. davevart

    davevart Foal

    Joined:
    Apr 30, 2015
    Messages:
    5
    Likes Received:
    0
    First lesson learnt is to spec my question better! I wish to find qualifiers where horses have run to a certain RFR in the 3 outings prior to this race . So in example

    Fin Position Horse Previous 3 RPR SP
    1 A 100 105 111 1.5
    2 B 0 100 97 4
    3 C 95 116 109 1

    Horse C is the only qualifier that meets having run to RFR > 115 in last 3 outings and my summary would be 1 run 0 wins -1 return

    Apologies if I misled you
     
  6. ArkRoyal

    ArkRoyal Administrator

    Joined:
    Dec 28, 2012
    Messages:
    12,615
    Likes Received:
    12,675
    Have a look at this file, I got it from the RI forum a few years ago. Things have moved on a little with 64 bit windows now being available but the underlying information is still valid.
    If you get stuck let me know and I will try and help. Once you have linked the RI tables to access you can start to write your own queries.

    AR
     

    Attached Files:

  7. davevart

    davevart Foal

    Joined:
    Apr 30, 2015
    Messages:
    5
    Likes Received:
    0
    Thanks for that I've now got the rfr for every run of each horse that is declared overnight. How can I restrict this to just the last 3 runs for each horse and then to show only those with a rfr > 110

    SELECT decouting.dohid, decouting.dohname, outing.odate, outing.orf, decrace.drrtype, Left([outing].[orf],3) AS Expr1, decrace.drclass
    FROM decrace INNER JOIN (decouting INNER JOIN outing ON decouting.dohid = outing.ohorseid) ON decrace.drid = decouting.doraceid
    WHERE (((decrace.drrtype)="CHS") AND ((decrace.drclass)>"2"))
    ORDER BY decouting.dohname, outing.odate DESC;

    I presume it's via subqueries but I've played with that til I'm blue in the face with no success
     
  8. rpjd99

    rpjd99 Colt

    Joined:
    Dec 30, 2012
    Messages:
    631
    Likes Received:
    520
    @davevart
    Try this and see how it works out:
    Code:
    SELECT D.dohid, D.dohname, O.odate, O.orf, R.drrtype, Left(O.orf,3) AS Expr1, R.drclass
    FROM decrace R
    INNER JOIN decouting D ON D.doraceid=R.drid
    INNER JOIN outing O ON O.ohorseid=D.dohid
    WHERE R.drrtype = "CHS" AND R.drclass >2 --or '2' (single quotes) if it's a varchar data type
    ORDER BY D.dohname, O.odate DESC;
    Ray

    [Had to use the code box as "O dot o" is substituted by the smilie o_O in the normal reply box!]
     
    Last edited: May 9, 2015
  9. davevart

    davevart Foal

    Joined:
    Apr 30, 2015
    Messages:
    5
    Likes Received:
    0
    Ray

    Not sure what this gives me over the previous code....or am I just being stupid? I want the last three runs but only if one of them has a orf > 110
     
  10. ArkRoyal

    ArkRoyal Administrator

    Joined:
    Dec 28, 2012
    Messages:
    12,615
    Likes Received:
    12,675
    @davevart Have you seen this SQL Query Help The problem you will have is that MS Access does not use standard SQL in all cases which makes life tricky.

    I generally do a 'big' query in Access to get all of the raw data and then export ot as an excel sheet and write some VBA to extract what I need. Obviously not the most efficient way to go but it works for me and I didn't have to solve the non-standard way Access does things!
     
    rpjd99 likes this.
  11. rpjd99

    rpjd99 Colt

    Joined:
    Dec 30, 2012
    Messages:
    631
    Likes Received:
    520
    @davevart ,

    Kevin's advice is very sound and it's what I do when writing VBA analysis routines or developing a complex SQL query. That way I'll always know what the correct result should be. I don't use Access, only SQL Server, so I don't know where Access SQL differs from standard SQL. I found your JOIN clauses strange, as I never came across this in SQL. But maybe it's the way Access SQL does it. In standard SQL, only single quotes are used for char/varchar strings, so "CHS" should have read 'CHS'. My mistake.

    Still, the query as written will extract all chases(?) of class 2(?) or lower. If you want only the latest three races where the OR is greater than 110, you'll have to add this to the WHERE clause in a way that Access will recognise it or use the TOP command in the SELECT clause, as suggested by @leftinthestalls in post 2 above.

    Ray
     
  12. ArkRoyal

    ArkRoyal Administrator

    Joined:
    Dec 28, 2012
    Messages:
    12,615
    Likes Received:
    12,675
    @davevart if you change your query to create a table called Last3

    Code:
    SELECT decouting.dohid, decouting.dohname, outing.odate, outing.orf, decrace.drrtype, Left([outing].[orf],3) AS Expr1, decrace.drclass INTO Last3
    FROM decrace INNER JOIN (decouting INNER JOIN outing ON decouting.dohid = outing.ohorseid) ON decrace.drid = decouting.doraceid
    WHERE (((decrace.drrtype)="CHS") AND ((decrace.drclass)>"2"))
    ORDER BY decouting.dohname, outing.odate DESC;
    You can then run this query to get the last 3 runs

    Code:
    SELECT *
      FROM Last3 t
    WHERE odate IN
    (
      SELECT TOP 3 odate
        FROM Last3
       WHERE dohid = t.dohid
       ORDER BY  odate DESC
    )
    ORDER BY dohid, odate DESC;
     
  13. Nclad

    Nclad Foal

    Joined:
    Nov 28, 2015
    Messages:
    65
    Likes Received:
    60
    @ArkRoyal Hi, i've just linked up to Raceforms database tables today, would you mind just lending a little help please.....

    Am i right in saying they seem to be non indexed?
    Do i need to set the primary keys and build the relationships manually or leave them with no primary keys?
    If i change a field name will this effect the updates?

    Any help is welcome

    Thanks.
     
  14. ArkRoyal

    ArkRoyal Administrator

    Joined:
    Dec 28, 2012
    Messages:
    12,615
    Likes Received:
    12,675
    If you have linked the tables you are simply 'seeing' that data within Access and cannot change any of the data or field names.
     
    Nclad likes this.
  15. Nclad

    Nclad Foal

    Joined:
    Nov 28, 2015
    Messages:
    65
    Likes Received:
    60
    I've realised that now. Thanks for reply
     
  16. Nclad

    Nclad Foal

    Joined:
    Nov 28, 2015
    Messages:
    65
    Likes Received:
    60
    hi @ArkRoyal and @rpjd99 , I'm trying to write a query that will become similar to the trainer report on HRB which i can then do Index Match on from Excel to get the info that I need.

    So i've started with runs in last 365 days but its not returning the correct answer, this is what i've got.

    Code:
    SELECT trainer.tstylename, Sum(IIf([tid]=[otrainerid] And [odate]>(Date()-365),1,0)) AS Runs
    FROM outing INNER JOIN ((decouting INNER JOIN trainer ON decouting.dotstrip = trainer.tstrip) INNER JOIN decrace ON decouting.doraceid = decrace.drid) ON (decouting.dohid = outing.ohorseid) AND (outing.otrainerid = trainer.tid)
    GROUP BY trainer.tstylename;
    
    Am i doing something obviously wrong, any help would be great.
     
  17. ArkRoyal

    ArkRoyal Administrator

    Joined:
    Dec 28, 2012
    Messages:
    12,615
    Likes Received:
    12,675
    What is 'wrong' with the returned answer?
     
  18. Nclad

    Nclad Foal

    Joined:
    Nov 28, 2015
    Messages:
    65
    Likes Received:
    60
    It seems to be only returning count from the last 10 days from what i can work out. I think I've got round it by removing the decouting and decrace tables and only linking the Trainer, outing and course tables, and then using criteria of only count runs on GB or IRE course. This now gives the correct answers but is working it out for 3000 odd trainers so is a bit slow.

    Is this the easiest way to create this Trainer Report that i can pull info from to use in the individual Racecards that I'm creating or am i going about it the hard way in your opinion?

    Have ordered the "2013 MS Access SQL" book, should be here Tuesday, so just try and muddle through till then.
     
  19. Nclad

    Nclad Foal

    Joined:
    Nov 28, 2015
    Messages:
    65
    Likes Received:
    60
    @ArkRoyal this is what i've got just for the previous 365days.
    Code:
    SELECT trainer.tstylename, Count(IIf([tid]=[otrainerid] And [odate]>(Date()-365),1)) AS Runs365, Count(IIf([tid]=[otrainerid] And Val([opos])=1 And [odate]>(Date()-365),1)) AS Wins365, Count(IIf([tid]=[otrainerid] And Val([opos])=2 And [odate]>(Date()-365),1)) AS 2nd365, Count(IIf([tid]=[otrainerid] And Val([opos])=3 And [odate]>(Date()-365),1)) AS 3rd365, Sum(IIf([odate]<(Date()-365),0,IIf([tid]=[otrainerid] And Val([opos])=1 And [odate]>(Date()-365),[ospval],-1))) AS [P/L365], Round(Sum(IIf([odate]<(Date()-365),0,IIf([tid]=[otrainerid] And [odate]>(Date()-365),1/([ospval]+1)))),2) AS Exp365
    FROM course INNER JOIN (outing INNER JOIN trainer ON outing.otrainerid = trainer.tid) ON course.cid = outing.ocourseid
    WHERE (((course.ccountry)="GB" Or (course.ccountry)="IRE"))
    GROUP BY trainer.tstylename;
    
     
  20. ArkRoyal

    ArkRoyal Administrator

    Joined:
    Dec 28, 2012
    Messages:
    12,615
    Likes Received:
    12,675
    DidN'T See your last post, this is what I had for your first attempt.
    Code:
    SELECT trainer.tstylename, Sum(IIf([tid]=[otrainerid] And [odate]>(Date()-365),1,0)) AS Runs
    FROM outing INNER JOIN ((decouting INNER JOIN trainer ON decouting.dotstrip = trainer.tstrip) INNER JOIN decrace ON decouting.doraceid = decrace.drid) ON outing.otrainerid = trainer.tid
    GROUP BY trainer.tstylename;
     
    Nclad likes this.
  21. Nclad

    Nclad Foal

    Joined:
    Nov 28, 2015
    Messages:
    65
    Likes Received:
    60
    Yes, for some reason yours has gone wrong but in a different way, take for instance Dan Skelton, its returning the answer 2172 but there is only 1845 runs for Skelton on the whole database and this figure is only supposed to be for last 365 days. I think its because Dan Skelton is listed on the DecOutings 3 times so it is taking the answer that should be 724 and adding it together 3 times. Not sure how to get round this.
     
  22. ArkRoyal

    ArkRoyal Administrator

    Joined:
    Dec 28, 2012
    Messages:
    12,615
    Likes Received:
    12,675
    Yes of course, that is what is happening. We need to use a sub-query to just pick up the trainers once from the decouting table irrespective of the numbers of runners they have that day.
     
    Nclad likes this.
  23. Nclad

    Nclad Foal

    Joined:
    Nov 28, 2015
    Messages:
    65
    Likes Received:
    60
    Result, managed to just retrieve one of each namefrom the decouting table using max(dodate).
    Thanks for your help @ArkRoyal
     
    ArkRoyal likes this.
  24. ArkRoyal

    ArkRoyal Administrator

    Joined:
    Dec 28, 2012
    Messages:
    12,615
    Likes Received:
    12,675
    Would you mind posting the query you came up with for completeness?
     
  25. Nclad

    Nclad Foal

    Joined:
    Nov 28, 2015
    Messages:
    65
    Likes Received:
    60
    I think i might of jumped the gun a bit, its still multiplying by the amount of times the trainer is on the decouting list. Working on it at the minute, I will work it out(eventually) and when i do i will post it. I can either get the correct answers but can only narrow it down to 3000 odd trainers or i can get the right amount of trainers with the wrong answers. Typical.... I shall continue.
     
    ArkRoyal likes this.
  26. Nclad

    Nclad Foal

    Joined:
    Nov 28, 2015
    Messages:
    65
    Likes Received:
    60
    @ArkRoyal - Job Done, had to create a separate list which is i'm guessing what you refer to as a subquery, took a while to be able to filter it correctly.
    Code:
    SELECT decouting.dotname AS DecTrainer
    FROM decouting
    GROUP BY decouting.dotname;
    
    And then referenced it into the next query, this code is quite big as i've been adding catergories, so far done 365days, 14days, Month were in over last 2 years. Still got alot more to add but hopefully progress will speed up a bit now.

    Code:
    SELECT DISTINCTROW ListDecTrainer.DecTrainer AS TrainerName, Count(IIf([tid]=[otrainerid] And [odate]>(Date()-365),1)) AS Runs365, Count(IIf([tid]=[otrainerid] And Val([opos])=1 And [odate]>(Date()-365),1)) AS Wins365, Count(IIf([tid]=[otrainerid] And Val([opos])=2 And [odate]>(Date()-365),1)) AS 2nd365, Count(IIf([tid]=[otrainerid] And Val([opos])=3 And [odate]>(Date()-365),1)) AS 3rd365, Sum(IIf([odate]<(Date()-365),0,IIf([tid]=[otrainerid] And Val([opos])=1 And [odate]>(Date()-365),[ospval],-1))) AS [P/L365], Round(Sum(IIf([odate]<(Date()-365),0,IIf([tid]=[otrainerid] And [odate]>(Date()-365),1/([ospval]+1)))),2) AS Exp365, Count(IIf([tid]=[otrainerid] And [odate]>(Date()-14),1)) AS Runs14, Count(IIf([tid]=[otrainerid] And Val([opos])=1 And [odate]>(Date()-14),1)) AS Wins14, Count(IIf([tid]=[otrainerid] And Val([opos])=2 And [odate]>(Date()-14),1)) AS 2nd14, Count(IIf([tid]=[otrainerid] And Val([opos])=3 And [odate]>(Date()-14),1)) AS 3rd14, Sum(IIf([odate]<(Date()-30),0,IIf([tid]=[otrainerid] And Val([opos])=1 And [odate]>(Date()-14),[ospval],-1))) AS [P/L14], Round(Sum(IIf([odate]<(Date()-30),0,IIf([tid]=[otrainerid] And [odate]>(Date()-14),1/([ospval]+1)))),2) AS Exp14, Count(IIf([tid]=[otrainerid] And DatePart("m",[odate])=Month(Date()) And [odate]>(Date()-730),1)) AS RunsMonth, Count(IIf([tid]=[otrainerid] And Val([opos])=1 And DatePart("m",[odate])=Month(Date()) And [odate]>(Date()-730),1)) AS WinsMonth, Count(IIf([tid]=[otrainerid] And Val([opos])=2 And DatePart("m",[odate])=Month(Date()) And [odate]>(Date()-730),1)) AS 2ndMonth, Count(IIf([tid]=[otrainerid] And Val([opos])=3 And DatePart("m",[odate])=Month(Date()) And [odate]>(Date()-730),1)) AS 3rdMonth, Sum(IIf(DatePart("m",[odate])=Month(Date()),IIf([tid]=[otrainerid] And Val([opos])=1 And [odate]>(Date()-730),[ospval],-1),0)) AS [P/LMonth], Round(Sum(IIf(DatePart("m",[odate])=Month(Date()),IIf([tid]=[otrainerid] And [odate]>(Date()-730),1/([ospval]+1)))),2) AS ExpMonth
    FROM ListDecTrainer INNER JOIN (outing INNER JOIN trainer ON outing.otrainerid = trainer.tid) ON ListDecTrainer.DecTrainer = trainer.tstylename
    GROUP BY ListDecTrainer.DecTrainer;
    
     
    ArkRoyal likes this.

Share This Page