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!

Access or Excel.....Help Please

Discussion in 'Databases' started by Nclad, Oct 5, 2016.

  1. Nclad

    Nclad Foal

    Joined:
    Nov 28, 2015
    Messages:
    50
    Likes Received:
    46
    My Excel spreadsheet which was acting as my database has grown to the point where the lights dim in the house when i try to do anything with it. So.... I've transferred all the data into access which runs like a dream, much quicker. But here's where my problems start as today was the first time i'd even opened the program up, which was about 20 minutes after my Access 2016 for Dummies arrived from Amazon.

    Now what? Do people tend to interrogate their databases from Excel, or can Access break everything down as easy as Excel can. Has Access got the same capabilities as Excel has for the analysis breakdowns. I've had a flick through the new book and it mentions queries and reports and wondered if this was the route people took instead of using Excel as well.

    Any help, advice or thoughts would be appreciated.

    Thanks
    Kev
     
  2. rpjd99

    rpjd99 Colt

    Joined:
    Dec 30, 2012
    Messages:
    631
    Likes Received:
    516
    The advantage of using Excel to interrogate Access is that you can format the output from Access the way you want it via macros. I use MS SQL Server, which is similar to Access, and pulling data into Excel is very fast. What I've found is that I use Excel and a database viewer, QlikView (free for personal use), to interrogate the DB most of the time, but I also programme directly in SQL Server's interface (SSMS). It just depends on what I need at the time. It'll probably make more sense if I explain step-by-step how I use it:
    1. Excel to get results, process them, error-check them and export them to text files.
    2. SSMS to import the text files to the database, do some checking of database integrity and add various counters to the results data.
    3. Excel to get the day's race cards, interrogate the DB to carry out analysis of selected races and display the analysis in Excel.
    4. QlikView - set up to give me a HRB-like interface - to explore experimental trends and drill-down analysis.
    5. SSMS for database management, adding new functionality to the import routines and for quick exploratory looks.

    I imagine you'll evolve your own way of doing it. It's a lot of work at the beginning, but I think you'll find it's worth it.

    Ray
     
    Nclad likes this.
  3. SteveT

    SteveT Colt

    Joined:
    Mar 12, 2016
    Messages:
    801
    Likes Received:
    628
    hi @Nclad
    I'm nowhere near rays standards but wish i could do those things but here's a link that my help you as well What is Access? - Access
    and i'm sure there are a lot more people on here who can help you better than me as i'm in the same boat but what i have been doing is just playing about creating new databases and see what i can do putting in some old racing stuff creating relationships between tables then try and query that.
    Just now what i am doing is trying to make tables for each range like trainer, jockey, horse, course but there is a lot of redundancy in racing data as most is repeated and access works best with the redundant data removed if you see what i mean.
    cheers
    steve
     
    nagwa and Nclad like this.
  4. ArkRoyal

    ArkRoyal Administrator

    Joined:
    Dec 28, 2012
    Messages:
    12,549
    Likes Received:
    12,357
    You probably want to add a table for "Races" as well as this would reduce the repeated information considerably.
     
    Nclad and SteveT like this.
  5. rpjd99

    rpjd99 Colt

    Joined:
    Dec 30, 2012
    Messages:
    631
    Likes Received:
    516
    Sorry to disappoint you, but I don't have any standard! I started from scratch about 4/5 years ago and was on the point of giving up on SQL Server when @ArkRoyal showed me the folly of my thinking! I just started with two tables, one a race directory and the other a runners directory. With experimentation, I soon realised the data redundancy @SteveT refers to and I broke the tables up into what I have today, as shown in the attached file. Hope you can get some ideas from it, @Nclad .

    Ray
     

    Attached Files:

    TripleFirst, nagwa, Nclad and 3 others like this.
  6. ArkRoyal

    ArkRoyal Administrator

    Joined:
    Dec 28, 2012
    Messages:
    12,549
    Likes Received:
    12,357
    You are being too kind Ray, I think in reality we were the blind leading the blind and between us managed to see a chink of light at the end of a long tunnel.
     
    rpjd99 likes this.
  7. Nclad

    Nclad Foal

    Joined:
    Nov 28, 2015
    Messages:
    50
    Likes Received:
    46
    Some very helpful responses, thank you.

    Seems like I've got a busy spell coming up with learning all this.

    @rpjd99 That is an amazing amount of information. Maybe I need to upscale somewhat! Does all this info come from HRB site or is it a collection from several sources? I grab everything I can using web queries and the daily sheets for trainer or weight etc from HRB, but haven't figured out how to grab the stuff from pages like the horse profiler as they don't seem to have individual web addresses.

    Also with the database I've created, it's a flat sheet database, so basically a spreadsheet that now works at a reasonable speed, both you and @SteveT sound like you've gone the multi table route. Is this something which would benefit me?

    Thanks again for the help guys.
     
  8. ArkRoyal

    ArkRoyal Administrator

    Joined:
    Dec 28, 2012
    Messages:
    12,549
    Likes Received:
    12,357
    If you are now able to use your data without dimmimg the lights then perhaps its job done. To get the most out of a DB then for something like horse racing multi-tables is the way to go. However, it might be worth seeing how you get on with your current setup to try and get a feeling of what you can and cannot do with a single Access table before taking the plunge.
     
    Nclad likes this.
  9. Nclad

    Nclad Foal

    Joined:
    Nov 28, 2015
    Messages:
    50
    Likes Received:
    46
    Yep, think you're right, for now I need to concentrate on getting my head round access, but I always like to plan ahead.
     
  10. rpjd99

    rpjd99 Colt

    Joined:
    Dec 30, 2012
    Messages:
    631
    Likes Received:
    516
    No, none of it comes from HRB, as it's a French racing database. The results and race cards are scraped using an Excel macro, missing/anomalous/bad data identified using another macro and then corrected (manually) from the France Galop site (French BHA).

    I do have a lot of calculated columns added to the database, some done in Excel during the scrape and the rest done in SQL during import. It started small and simple and was gradually built up to what it is today. If you're prepared to stick at it, you'll be fully at ease with all the programming before you're twice married!

    I'm not au fait with how Access handles indexes (the primary and related foreign keys I show in the file I attached earlier), but they really speed up queries in SQL Server and I imagine they would have the same effect for Access.

    If you need any help along the way, just shout.

    Ray
     
    Nclad likes this.
  11. Nclad

    Nclad Foal

    Joined:
    Nov 28, 2015
    Messages:
    50
    Likes Received:
    46
    Feel a bit stupid now, and funnily enough I did notice it was a French database, but didn't even put 2 and 2 together when I wrote about HRB.

    This is definitely more in depth than I imagined, but I love a challenge. I have no doubt I'll be asking for more help in the near future. Thanks for the offer, very kind.

    I'm sure the wife will be thrilled that I'm going to spend even more time on the computer. But at least she'll be able to watch the soaps without the lights dimming.
     
    SteveT and rpjd99 like this.
  12. SteveT

    SteveT Colt

    Joined:
    Mar 12, 2016
    Messages:
    801
    Likes Received:
    628
    hi @Nclad from my limited knowledge of access having a relational database ie separate tables makes it faster and more efficient when querying it as it's not going over the same horse name or trainer name hundreds of times because in a table you only have that piece of data once. I have found just making small ones to play about with has helped me understand better.
    cheers
    steve
     
    Nclad likes this.
  13. Nclad

    Nclad Foal

    Joined:
    Nov 28, 2015
    Messages:
    50
    Likes Received:
    46
    Thanks @SteveT, with all the advice I think I'm going to concentrate on seeing what I can do with what I've got at the minute, and work on the single table. But by the sounds of things the multi table is the way forward. I think the start with small multi tables first is the way to go in the future though. There's an awful lot to get my head round. Thanks again.
     
    SteveT likes this.
  14. SteveT

    SteveT Colt

    Joined:
    Mar 12, 2016
    Messages:
    801
    Likes Received:
    628
    you welcome @Nclad and yes your right there is, does my head in sometimes lol
    cheers
    steve
     
    Nclad likes this.
  15. rpjd99

    rpjd99 Colt

    Joined:
    Dec 30, 2012
    Messages:
    631
    Likes Received:
    516
    @Nclad ,

    When you're doing the inevitable googling to come, you'll usually find the above called 'normalisation'. See? You've already learnt something new...(!)

    Ray
     
    Nclad likes this.
  16. Nclad

    Nclad Foal

    Joined:
    Nov 28, 2015
    Messages:
    50
    Likes Received:
    46
    Hi Ray
    Well..... It didn't take long to realise how much you need multi table databases! @rpjd99 am I right in thinking that in your example of your database headings that the courses, horses, jockeys, owners and trainers are only added to with new entries where as runners and directories are added to daily with the race info?

    Also where you have Jockey ID, is their name not ID enough, what benefit does the ID have.

    Thanks Kev
     
  17. ArkRoyal

    ArkRoyal Administrator

    Joined:
    Dec 28, 2012
    Messages:
    12,549
    Likes Received:
    12,357
    ID's are unique, names may be the same which will cause problems.
     
    Nclad likes this.
  18. rpjd99

    rpjd99 Colt

    Joined:
    Dec 30, 2012
    Messages:
    631
    Likes Received:
    516
    That's correct. The courses are virtually static and will only be added to if an AW track is added (Longchamp 2017?) or has it's AW surface changed (Deauville 2014). In the directory and runners tables, no course, horse, trainer, jockey or owner names appear: only their index numbers. When I'm updating the DB with the results, a flag is added during import to indicate that a data entry is new, i.e. that the relevant index number does not already exist in the DB. So when I'm importing a day's results, all race details in the directory table and all runner details in the runners table will be flagged as new because the race index numbers in both tables can't exist in the DB. However, for the horses, trainers, jockeys and owners, their details will only be flagged where their index numbers do not exist already in the DB. This avoids variations in name-spelling, change of name on change of marital status (hyphen-hyphen), and, particularly with French, the use (or mis-use) of accents and diphthongs in names.

    Once all of the basic data has been imported to the DB, I open an Excel file that contains a complete catalogue of trainer, jockey and owner details. I then extract the new entries (flagged by 'T', 'J' and 'O') from the DB and check each new surname found for its existence in the DB. (I have buttons on the sheet and menu bar linked to macros that do the import and list every occurrence of the surname for me.) IRE/UK trainers will sometimes have a variation on the name that, in fact, already exists in the DB, but it will have been given a different index number by the site from where I get the results. So I choose one index number and/or name variation to use and I add this to a correction algo in SQL, which means that my DB will contain only one index number and one variation of the name. There are some exceptions to this, but I won't explain them here to avoid overcomplicating things.

    Well, put it this way: I've three variations noted for Jo Hughes and Nick Williams and five variations on the Magnier/Tabor/Smith owner partnership. With a single index number and single name variation in my DB, whenever one of the erroneous index numbers appears it's automatically corrected by the correction algo in SQL.

    If any of this is not clear, feel free to ask.

    Ray
     
    markfinn likes this.
  19. Nclad

    Nclad Foal

    Joined:
    Nov 28, 2015
    Messages:
    50
    Likes Received:
    46
    Thank you for the detailed reply, that makes much more sense now.

    I'm still trying to create an excel sheet which will sort the data into the relevant tables pre import to DB. Its taking a long time but want to make sure i get it right, with the work that seems to be involved i don't want to be doing it too many times. I have ordered a book to try help as i don't want to keep hassling you (coming today according to my email from Amazon, amazing how late delivery men work nowadays?:confused:), and sometimes it takes so long to find exactly what you want on google, especially when you're not sure what you need to start with. Can i just ask about the flagging method you use. Is this done at excel level or DB level, is it standard or something you use. One of the things i'm not sure of is before you do your DB update each day, how do you know whether a Horse ID is already in the database or not. Is there an excel function to search a DB, or is this done using VBA?

    Many Thanks
    Kev
     
  20. rpjd99

    rpjd99 Colt

    Joined:
    Dec 30, 2012
    Messages:
    631
    Likes Received:
    516
    No worries on that score. I'm retired and 24 hours of each day belong to me and I decide to whom and to what I give those hours. So you'll know if you're hassling me when I don't reply...!

    This is the code I use for the initial import:
    Code:
    USE FrenchDB;
    
    PRINT GETDATE()
    
    ------------------------------------------------------------------------------------------
    -- ******** MAKE SURE THE YEAR IS CORRECTLY SET IN THE HORSE TABLE UPDATE CODE ******** --
    ------------------------------------------------------------------------------------------
    IF OBJECT_ID('t_Dir') IS NOT NULL DROP TABLE t_Dir
    ;
    CREATE TABLE t_Dir
    (
    D1900 INT NOT NULL,
    WDay CHAR(3) NOT NULL,
    MDay TINYINT NOT NULL,
    Mth CHAR(3) NOT NULL,
    Yr SMALLINT NOT NULL,
    CrsID INT NOT NULL,
    Hippodrome VARCHAR(32) NOT NULL,
    Reg VARCHAR(2) NOT NULL,
    Cat TINYINT NOT NULL,
    NbMeet TINYINT,
    QUH CHAR(1) NOT NULL,
    WX VARCHAR(64),
    Temp SMALLINT,
    GenGng VARCHAR(64),
    NbRce TINYINT NOT NULL,
    RceTime CHAR(5),
    RceID INT NOT NULL,
    RaceName VARCHAR(128) NOT NULL,
    Going VARCHAR(64),
    RceTyp VARCHAR(1) NOT NULL,
    Dist SMALLINT NOT NULL,
    Class VARCHAR(9),
    RceSex CHAR(1),
    Sang CHAR(3),
    EBF CHAR(4),
    MnAge TINYINT,
    MxAge TINYINT,
    HcRef VARCHAR(9),
    Dir CHAR(1) NOT NULL,
    Surf VARCHAR(12),
    Trk VARCHAR(3),
    Box CHAR(1),
    Pot TINYINT,
    Lice SMALLINT,
    Rider VARCHAR(13),
    Prize1 INT NOT NULL,
    Prize2 INT,
    Prize3 INT,
    Prize4 INT,
    Prize5 INT,
    Prize6 INT,
    Prize7 INT,
    Prize8 INT,
    TotPrize INT NOT NULL,
    Ran TINYINT NOT NULL,
    WinSec DECIMAL(5,2),
    RKWSec DECIMAL(5,2),
    TotOSP DECIMAL(4,1),
    TotPMU DECIMAL(4,1),
    TotLT DECIMAL(4,1),
    RceCond VARCHAR(2047)
    )
    ;
    PRINT 'Inserting data into t_Dir'
    BULK INSERT t_Dir
    FROM 'C:\Users\Administrator\Desktop\PT Data\Directory.txt'
    WITH
    (
    CODEPAGE = 'ACP',
    FIELDTERMINATOR = '|',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2
    )
    ;
    ALTER TABLE t_Dir ADD DNew CHAR(1) NOT NULL DEFAULT('D')
    ;
    IF OBJECT_ID('t_CID') IS NOT NULL DROP TABLE t_CID
    ;
    SET NOCOUNT ON
    SELECT RceID, CrsID AS CourseID
    INTO t_CID
    FROM t_Dir
    SET NOCOUNT OFF
    ;
    
    ---------------------------------------------------------------------------------------------------
    IF OBJECT_ID('t_Run') IS NOT NULL DROP TABLE t_Run
    ;
    CREATE TABLE t_Run
    (
    RceID INT NOT NULL,
    R1900 INT NOT NULL,
    Gain INT,
    FinPos VARCHAR(15) NOT NULL,
    Nb TINYINT NOT NULL,
    EcuSup VARCHAR(3),
    Horse VARCHAR(25) NOT NULL,
    Brd VARCHAR(3) NOT NULL,
    ReclPr VARCHAR(6),
    Gear VARCHAR(3),
    HSex CHAR(1) NOT NULL,
    Age TINYINT,
    Draw TINYINT,
    WtK DECIMAL(3,1),
    WonBy DECIMAL(4,2),
    DTP DECIMAL(4,2),
    DTW DECIMAL(5,2),
    Trainer VARCHAR(40) NOT NULL,
    Jockey VARCHAR(40) NOT NULL,
    OpnSP DECIMAL(5,1),
    FinSP DECIMAL(6,2),
    LTFSP DECIMAL(6,2),
    PlcSP DECIMAL(6,2),
    LTPlc DECIMAL(6,2),
    Comments VARCHAR(256),
    WtRnk TINYINT,
    [OR] DECIMAL(3,1),
    ORRnk TINYINT,
    OSPRnk TINYINT,
    FSPRnk TINYINT,
    LTRnk TINYINT,
    Ownr VARCHAR(64) NOT NULL,
    Sire VARCHAR(25),
    SireBrd VARCHAR(3),
    Dam VARCHAR(25),
    DamBrd VARCHAR(3),
    DamSire VARCHAR(25),
    DSBrd VARCHAR(3),
    Breeder VARCHAR(127),
    HorseID INT NOT NULL,
    TrainerID INT NOT NULL,
    JockeyID INT NOT NULL,
    OwnrID INT NOT NULL,
    SireID INT,
    DamID INT,
    DSID INT,
    JClm DECIMAL(2,1),
    exJck INT,
    LdE VARCHAR(32),
    DoB INT,
    DoC INT
    )
    ;
    PRINT '------------------------------------' + CHAR(13) + 'Inserting data into t_Run'
    BULK INSERT t_Run
    FROM 'C:\Users\Administrator\Desktop\PT Data\Runners.txt'
    WITH
    (
    CODEPAGE = 'ACP',
    FIELDTERMINATOR = '|',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2
    )
    ;
    ALTER TABLE t_Run ADD RNew CHAR(1) NOT NULL DEFAULT('R')
    ALTER TABLE t_Run ADD CourseID INT NOT NULL DEFAULT(1)
    ;
    SET NOCOUNT ON
    MERGE t_Run AS T
    USING t_CID AS S
    ON S.RceID = T.RceID
    WHEN MATCHED THEN
    UPDATE SET T.CourseID = S.CourseID
    ;
    SET NOCOUNT OFF
    ;
    DROP TABLE t_CID
    ;
    
    ---------------------------------------------------------------------------------------------------
    IF OBJECT_ID('t_Lnk') IS NOT NULL DROP TABLE t_Lnk
    ;
    CREATE TABLE t_Lnk
    (
    RceID INT NOT NULL,
    Link VARCHAR(255) NOT NULL,
    NbRce TINYINT NOT NULL,
    [Description] VARCHAR(1023)
    )
    ;
    PRINT '------------------------------------' + CHAR(13) + 'Inserting data into t_Lnk'
    BULK INSERT t_Lnk
    FROM 'C:\Users\Administrator\Desktop\PT Data\Links.txt'
    WITH
    (
    CODEPAGE = 'ACP',
    FIELDTERMINATOR = '|',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2
    )
    ;
    ALTER TABLE t_Lnk ADD LNew CHAR(1) NOT NULL DEFAULT('L')
    ;
    
    ---------------------------------------------------------------------------------------------------
    -- MAKE SURE THE YEAR IS CORRECTLY SET HERE (IN 3 PLACES)
    ---------------------------------------------------------------------------------------------------
    PRINT '------------------------------------' + CHAR(13) + 'Updating the Horses table'
    
    SET NOCOUNT ON
    UPDATE Horses SET HNew = NULL WHERE HNew = 'H'
    SET NOCOUNT OFF
    ;
    MERGE Horses AS T
    USING (SELECT DISTINCT HorseID, Horse, Brd, HSex, Age, Breeder, Sire, SireBrd, SireID, Dam, DamBrd, DamID, DamSire, DSBrd, DSID, DoB, DoC FROM t_Run) AS S
    ON S.HorseID = T.HorseID
    --                                                                                                 HERE
    WHEN MATCHED THEN UPDATE SET T.LastYear = 2016
    WHEN NOT MATCHED BY TARGET THEN
    INSERT (HorseID, Horse, Bred, Sex, YOB, Breeder, Sire, SireBrd, SireID, Dam, DamBrd, DamID, DamSire, DSBrd, DSID, HNew, LastYear, DoB, DoC)
    --                                                                                                                                                                           HERE
    VALUES (S.HorseID, S.Horse, S.Brd, CASE WHEN S.HSex = 'H' THEN 'M' ELSE S.HSex END, 2016-S.Age, S.Breeder, S.Sire, S.SireBrd, S.SireID, S.Dam, S.DamBrd, S.DamID, S.DamSire, S.DSBrd,                            HERE
    S.DSID, 'H', 2016, S.DoB, S.DoC)
    ;
    
    ---------------------------------------------------------------------------------------------------
    PRINT '------------------------------------' + CHAR(13) + 'Updating the Jockeys table'
    
    SET NOCOUNT ON
    UPDATE Jockeys SET JNew = NULL WHERE JNew = 'J'
    SET NOCOUNT OFF
    ;
    MERGE Jockeys AS T
    USING (SELECT DISTINCT JockeyID, Jockey FROM t_Run) AS S
    ON S.JockeyID = T.JockeyID
    WHEN NOT MATCHED BY TARGET THEN
    INSERT (JockeyID, Jockey, JNew)
    VALUES (S.JockeyID, S.Jockey, 'J')
    ;
    
    ---------------------------------------------------------------------------------------------------
    PRINT '------------------------------------' + CHAR(13) + 'Updating the Owners table'
    
    SET NOCOUNT ON
    UPDATE Owners SET ONew = NULL WHERE ONew = 'O'
    SET NOCOUNT OFF
    ;
    MERGE Owners AS T
    USING (SELECT DISTINCT OwnrID, Ownr FROM t_Run) AS S
    ON S.OwnrID = T.OwnrID
    WHEN NOT MATCHED BY TARGET THEN
    INSERT (OwnrID, Ownr, ONew)
    VALUES (S.OwnrID, S.Ownr, 'O')
    ;
    
    ---------------------------------------------------------------------------------------------------
    PRINT '------------------------------------' + CHAR(13) + 'Updating the Trainers table'
    
    SET NOCOUNT ON
    UPDATE Trainers SET TNew = NULL WHERE TNew = 'T'
    SET NOCOUNT OFF
    ;
    MERGE Trainers AS T
    USING (SELECT DISTINCT TrainerID, Trainer FROM t_Run) AS S
    ON S.TrainerID = T.TrainerID
    WHEN NOT MATCHED BY TARGET THEN
    INSERT (TrainerID, Trainer, TNew)
    VALUES (S.TrainerID, S.Trainer, 'T')
    ;
    
    ---------------------------------------------------------------------------------------------------
    PRINT '------------------------------------' + CHAR(13) + 'Updating the Directory table'
    
    SET NOCOUNT ON
    UPDATE Directory SET DNew = NULL WHERE DNew = 'D'
    SET NOCOUNT OFF
    ;
    --IF Directory AND t_Dir HAVE A DIFFERENT NUMBER OF COLS, THE COLUMNS TO BE IMPORTED MUST BE SPECIFIED IN THE INSERT COMMAND AS WELL
    --AT THE MOMENT THEY HAVE THE SAME NUMBER, BUT THAT MAY NOT ALWAYS BE THE CASE, SO THEY ARE SPECIFIED HERE TO ALLOW FOR FUTURE CHANGES
    INSERT INTO Directory (D1900, WDay, MDay, Mth, Yr, CrsID, NbMeet, QUH, WX, Temp, GenGng, NbRce, RceTime, RceID, RaceName, Going, RceTyp, Dist, Class,
           RceSex, Sang, EBF, MnAge, MxAge, HcRef, Dir, Trk, Box, Pot, Lice, Rider, Prize1, TotPrize, Ran, WinSec, RKWSec, TotOSP, TotPMU, TotLT, RceCond, DNew)
    --OUTPUT INSERTED.RceID
    SELECT D1900, WDay, MDay, Mth, Yr, CrsID, NbMeet, QUH, WX, Temp, GenGng, NbRce, RceTime, RceID, RaceName, Going, RceTyp, Dist, Class,
           RceSex, Sang, EBF, MnAge, MxAge, HcRef, Dir, Trk, Box, Pot, Lice, Rider, Prize1, TotPrize, Ran, WinSec, RKWSec, TotOSP, TotPMU, TotLT, RceCond, DNew
    FROM t_Dir
    ;
    DROP TABLE t_Dir
    ;
    
    ---------------------------------------------------------------------------------------------------
    PRINT '------------------------------------' + CHAR(13) + 'Updating the Links table'
    
    SET NOCOUNT ON
    UPDATE Links SET LNew = NULL WHERE LNew = 'L'
    SET NOCOUNT OFF
    ;
    INSERT INTO Links (RceID, Link, NbRce, [Description], LNew)
    SELECT RceID, Link, NbRce, [Description], LNew
    FROM t_Lnk
    ;
    DROP TABLE t_Lnk
    ;
    
    ---------------------------------------------------------------------------------------------------
    PRINT '------------------------------------' + CHAR(13) + 'Updating the Runners table'
    
    SET NOCOUNT ON
    UPDATE Runners SET RNew = NULL WHERE RNew = 'R'
    SET NOCOUNT OFF
    ;
    --AS Runners AND t_Run HAVE A DIFFERENT NUMBER OF COLS (Runners CONTAINS DSLR, LTO, LTO3, NTO3, 'z' COUNTER COLUMNS AND JOCKEY ALLOWANCES), THE COLUMNS TO BE IMPORTED MUST BE SPECIFIED IN THE INSERT COMMAND
    INSERT INTO Runners (RceID, R1900, Gain, FinPos, Nb, EcuSup, ReclPr, Gear, Age, Draw, WtK, WonBy, DTP, DTW, OpnSP, FinSP, LTFSP, PlcSP, LTPlc, Comments, WtRnk, [OR], ORRnk,
           OSPRnk, FSPRnk, LTRnk, HorseID, TrainerID, JockeyID, OwnrID, RNew, CourseID, HSex, JClm)
    SELECT RceID, R1900, Gain, FinPos, Nb, EcuSup, ReclPr, Gear, Age, Draw, WtK, WonBy, DTP, DTW, OpnSP, FinSP, LTFSP, PlcSP, LTPlc, Comments, WtRnk, [OR], ORRnk,
           OSPRnk, FSPRnk, LTRnk, HorseID, TrainerID, JockeyID, OwnrID, RNew, CourseID, HSex, JClm
    FROM t_Run
    ;
    
    ---------------------------------------------------------------------------------------------------
    PRINT '------------------------------------' + CHAR(13) + 'Calculating DSLR, LTO, LTO3 and NTO3'
    
    IF OBJECT_ID('z_DSLR') IS NOT NULL DROP TABLE z_DSLR
    ;
    SET NOCOUNT ON
    SELECT HorseID, RceID, R1900, FinPos,
        DSLR = CASE WHEN ROW_NUMBER() OVER (PARTITION BY HorseID ORDER BY R1900) > 1
                    THEN R1900 - LAG(R1900) OVER (PARTITION BY HorseID ORDER BY R1900)
                    ELSE 0
               END,
        LTO = CASE WHEN ROW_NUMBER() OVER (PARTITION BY HorseID ORDER BY R1900) > 1
                   THEN dbo.rfn_OneCharFinPos(LAG(FinPos,1) OVER (PARTITION BY HorseID ORDER BY R1900))
                   ELSE '<fto>'
              END,
        LTO3 = CASE WHEN ROW_NUMBER() OVER (PARTITION BY HorseID ORDER BY R1900) > 3
                    THEN dbo.rfn_OneCharFinPos(LAG(FinPos,3) OVER (PARTITION BY HorseID ORDER BY R1900))
                        + dbo.rfn_OneCharFinPos(LAG(FinPos,2) OVER (PARTITION BY HorseID ORDER BY R1900))
                        + dbo.rfn_OneCharFinPos(LAG(FinPos,1) OVER (PARTITION BY HorseID ORDER BY R1900))
                    WHEN ROW_NUMBER() OVER (PARTITION BY HorseID ORDER BY R1900) > 2
                    THEN dbo.rfn_OneCharFinPos(LAG(FinPos,2) OVER (PARTITION BY HorseID ORDER BY R1900))
                        + dbo.rfn_OneCharFinPos(LAG(FinPos,1) OVER (PARTITION BY HorseID ORDER BY R1900))
                    WHEN ROW_NUMBER() OVER (PARTITION BY HorseID ORDER BY R1900) > 1
                    THEN dbo.rfn_OneCharFinPos(LAG(FinPos,1) OVER (PARTITION BY HorseID ORDER BY R1900))
                    ELSE '<fto>'
               END,
        NTO3 = CASE WHEN COUNT(*) OVER (PARTITION BY HorseID) - ROW_NUMBER() OVER (PARTITION BY HorseID ORDER BY R1900) > 2
                    THEN dbo.rfn_OneCharFinPos(LEAD(FinPos,1) OVER (PARTITION BY HorseID ORDER BY R1900))
                        + dbo.rfn_OneCharFinPos(LEAD(FinPos,2) OVER (PARTITION BY HorseID ORDER BY R1900))
                        + dbo.rfn_OneCharFinPos(LEAD(FinPos,3) OVER (PARTITION BY HorseID ORDER BY R1900))
                    WHEN COUNT(*) OVER (PARTITION BY HorseID) - ROW_NUMBER() OVER (PARTITION BY HorseID ORDER BY R1900) > 1
                    THEN dbo.rfn_OneCharFinPos(LEAD(FinPos,1) OVER (PARTITION BY HorseID ORDER BY R1900))
                        + dbo.rfn_OneCharFinPos(LEAD(FinPos,2) OVER (PARTITION BY HorseID ORDER BY R1900))
                    WHEN COUNT(*) OVER (PARTITION BY HorseID) - ROW_NUMBER() OVER (PARTITION BY HorseID ORDER BY R1900) > 0
                    THEN dbo.rfn_OneCharFinPos(LEAD(FinPos,1) OVER (PARTITION BY HorseID ORDER BY R1900))
                    ELSE '<lto>'
               END
    INTO z_DSLR
    FROM Runners
    WHERE HorseID IN(SELECT DISTINCT HorseID FROM t_Run)
    GROUP BY HorseID, RceID, R1900, FinPos
    SET NOCOUNT OFF
    ;
    MERGE Runners AS T
    USING z_DSLR AS S
    ON S.RceID = T.RceID AND S.HorseID = T.HorseID
    WHEN MATCHED THEN
    UPDATE SET
        T.DSLR = S.DSLR, T.LTO = S.LTO, T.LTO3 = S.LTO3, T.NTO3 = S.NTO3
    ;
    DROP TABLE z_DSLR
    ;
    
    ---------------------------------------------------------------------------------------------------
    PRINT '------------------------------------' + CHAR(13) + 'Updating pOA'
    
    IF OBJECT_ID('z_pOA') IS NOT NULL DROP TABLE z_pOA
    ;
    WITH CTE AS
    (SELECT Gear, HorseID, R1900, RN = ROW_NUMBER() OVER(PARTITION BY HorseID ORDER BY R1900)
    FROM Runners
    WHERE HorseID IN(SELECT DISTINCT HorseID FROM t_Run) AND RIGHT(Gear,2) = 'OA'
    )
    SELECT * INTO z_pOA
    FROM CTE
    WHERE RN = 1
    ;
    MERGE Runners AS T
    USING z_pOA AS S
    ON S.HorseID = T.HorseID AND S.R1900 = T.R1900 AND NOT S.Gear = 'pOA'
    WHEN MATCHED THEN
    UPDATE SET T.Gear = 'pOA'
    ;
    DROP TABLE z_pOA
    ;
    
    ---------------------------------------------------------------------------------------------------
    PRINT '------------------------------------' + CHAR(13) + 'Updating pO'
    
    IF OBJECT_ID('z_pO') IS NOT NULL DROP TABLE z_pO
    ;
    WITH CTE AS
    (SELECT Gear, HorseID, R1900, RN = ROW_NUMBER() OVER(PARTITION BY HorseID ORDER BY R1900)
    FROM Runners
    WHERE HorseID IN(SELECT DISTINCT HorseID FROM t_Run) AND RIGHT(Gear,1) = 'O'
    )
    SELECT * INTO z_pO
    FROM CTE
    WHERE RN = 1
    ;
    MERGE Runners AS T
    USING z_pO AS S
    ON S.HorseID = T.HorseID AND S.R1900 = T.R1900 AND NOT S.Gear = 'pO'
    WHEN MATCHED THEN
    UPDATE SET T.Gear = 'pO'
    ;
    DROP TABLE z_pO
    Have a look at the lines that start with "ALTER TABLE"; this will give you an idea of how the flags are used. Note, the 'Merge' command, really fast in SQL, is not available in Access, but this link will give you an idea of how to mimic it for Access: Using MERGE with MS-Access and SQL Server 2008

    Have a think about it and get back to me.

    Ray
     
    Last edited: Oct 11, 2016
    Nclad likes this.
  21. SteveT

    SteveT Colt

    Joined:
    Mar 12, 2016
    Messages:
    801
    Likes Received:
    628
    nagwa and Nclad like this.
  22. Nclad

    Nclad Foal

    Joined:
    Nov 28, 2015
    Messages:
    50
    Likes Received:
    46
    Evening Ray @rpjd99
    Thank you for the reply, I can see where the line starting with alter is and sort of understand, I think there's a whole lot of reading to be done between now and me understanding that properly. Still cracking on when I have time but most of it seems to be spent reading and googling at the minute.

    @SteveT , thank you very much for the book link, not the same as the one I just bought luckily. Already coming in handy. Cheers.

    Kev.
     
    SteveT likes this.
  23. valiant thor

    valiant thor Yearling

    Joined:
    Feb 10, 2016
    Messages:
    189
    Likes Received:
    268
    Hi @Nclad
    Just an idea, instead of learning Access( I had a go cant make head nor tails of it) have a go @ 'pandas' in python.
    Easy enough to learn :think: ,you can scrape your data straight from the web or extract from excell,convert it to CSV files, or SQL,even use it like 'R' for analysing your data , draw graphs etc, etc
    After not writing any proper python scrapers for databases for over 15 years, Im just trying to pick it up again in my free time and this pandas makes everything a lot easier to when I had to parse all my own stuff. (heres hoping the old braincells will fire up again :confused: )
     
    Last edited: Oct 12, 2016
  24. Nclad

    Nclad Foal

    Joined:
    Nov 28, 2015
    Messages:
    50
    Likes Received:
    46
    It's a good idea that I might well drop back onto as I've just had a look at the pandas python website. But I've bought a book for Access and a book for Qlikview so I've got no excuses and should really give it a go. It's quite hard to learn all this whilst realistically still learning how to pick horses. Hopefully I can pick this up quicker than the horses!!!!! My only hope is that once all this comes together one should help the other.
    But if all fails I will definitely be heading back to the python site, and probably back to amazon for a book on python!
    Thanks for the advice @valiant thor
    Kev.
     
    valiant thor likes this.
  25. SteveT

    SteveT Colt

    Joined:
    Mar 12, 2016
    Messages:
    801
    Likes Received:
    628
    Hi @Nclad
    im glad i could be of help, i really need to learn alot more myself and i have some books on python as well if you need them just ask they were free also.
    At the minute im trying to get my head around excel vba and have managed to make a scraper but it's nothing like what some of them have put up on here, very messy but it does work so i know i can do it even at my age lol.
    cheers
    steve
     
    Nclad likes this.
  26. valiant thor

    valiant thor Yearling

    Joined:
    Feb 10, 2016
    Messages:
    189
    Likes Received:
    268
    Hi @SteveT
    Youve done better than me, excel vba is another I cant get my head around for some reason :confused:
    @Nclad
    If you do decide to give pandas a go later on , I would use the Anaconda 2.7 package when putting python on your computer as it has all the bells and whistles pre loaded ;)
     
    Last edited: Oct 13, 2016
    Nclad likes this.
  27. SteveT

    SteveT Colt

    Joined:
    Mar 12, 2016
    Messages:
    801
    Likes Received:
    628
    Hi @valiant thor
    well i tell you mate it's not been easy but at least i am making progress especially with this site Bill Jelen
    it's been a great help, i have given up asking on excel forums because as soon as you tell them it is for horse racing they seem to ignore you after that.
    cheers
    steve
     
    Nclad, rpjd99 and valiant thor like this.
  28. valiant thor

    valiant thor Yearling

    Joined:
    Feb 10, 2016
    Messages:
    189
    Likes Received:
    268
    Your not wrong there,also some pythonistas seem reluctant to help as well. :stinker:
    For some reason anything to do with racing seems taboo,but if you say its for shares trading or stock charting they're only too willing to help, yet I would imagine share traders have destroyed more peoples pension funds and savings than bookies ever have, infact they make bookies look like father christmas :eek:
     
    Nclad, footysystems, *tom* and 2 others like this.
  29. Nclad

    Nclad Foal

    Joined:
    Nov 28, 2015
    Messages:
    50
    Likes Received:
    46
    Hello people,

    I've finally got my database built, but at the minute i feel like someone has given me a shiny new car to play with but refuses to give me the keys!

    I've been using Qlikview to interrogate the database and look for trends on certain criteria, which is good, but what i would like to do is build a query/report that can be exported to excel with the last 3 or 5 figures of speed figures/ratings or trainer data but only for todays runners. Could someone help me understand how this is done.

    Can i directly query the database from excel or is the report generated in access and exported?
    How do i only get data from the 3 most recent entries?
    How do i only request information from the horses that are running today?

    Any help would be much appreciated.

    Thanks
    Kev.
     
  30. ArkRoyal

    ArkRoyal Administrator

    Joined:
    Dec 28, 2012
    Messages:
    12,549
    Likes Received:
    12,357
    You can do both.
    MS Access 2007: Retrieve Top N records from a query
    This really depends on how you have the data stored, for my setup I run a query in Access that links a table containing todays runners(Decs) and a table containing all past races(outings) and for each horse in the Decs table include all of its past runs from the outings table
     
    Nclad likes this.
  31. ArkRoyal

    ArkRoyal Administrator

    Joined:
    Dec 28, 2012
    Messages:
    12,549
    Likes Received:
    12,357
    This is also a useful resource SQLZOO
     
    Nclad likes this.
  32. Nclad

    Nclad Foal

    Joined:
    Nov 28, 2015
    Messages:
    50
    Likes Received:
    46
    Thanks @ArkRoyal , thats great. So just to clarify, the Decs table would be emptied and renewed each day and then report created.

    Had a quick glance at the link too, looks interesting, give it a proper look tomorrow when i have more time. Is SQL relatively easy to pick up?

    Thanks
    Kev.
     
  33. ArkRoyal

    ArkRoyal Administrator

    Joined:
    Dec 28, 2012
    Messages:
    12,549
    Likes Received:
    12,357
    Correct
     
    Nclad likes this.

Share This Page