• Hi Guest Just in case you were not aware I wanted to highlight that you can now get a 20% discount on Inform Racing.
    Simply enter the coupon code ukbettingform when subscribing here.
    We have a lot of members who are existing users of Inform Racing so help is always available if needed.
    Best Wishes
    AR
  • Hi Guest Just in case you were not aware I wanted to highlight that you can now get a free 7 day trial of Horseracebase here.
    We have a lot of members who are existing users of Horseracebase so help is always available if needed, as well as dedicated section of the fourm here.
    Best Wishes
    AR

Access or Excel.....Help Please

Nclad

Yearling
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
 
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.
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
 
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.

You probably want to add a table for "Races" as well as this would reduce the repeated information considerably.
 
I'm nowhere near rays standards but wish i could do those things
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 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 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 Nclad .

Ray
 

Attachments

  • Database Structure.xlsx
    22.1 KB · Views: 68
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!
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.
 
Some very helpful responses, thank you.

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

rpjd99 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 SteveT sound like you've gone the multi table route. Is this something which would benefit me?

Thanks again for the help guys.
 
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.
 
Yep, think you're right, for now I need to concentrate on getting my head round access, but I always like to plan ahead.
 
Does all this info come from HRB site or is it a collection from several sources?
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
 
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.
 
Thanks SteveT 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.
 
having a relational database ie separate tables makes it faster and more efficient when querying
Nclad 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
 
Hi Ray
Well..... It didn't take long to realise how much you need multi table databases! rpjd99 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
 
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?
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.

Also where you have Jockey ID, is their name not ID enough, what benefit does the ID have.
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
 
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
 
i don't want to keep hassling you
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:
Evening Ray rpjd99 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 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.
 
Hi Nclad 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:
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 valiant thor
Kev.
 
Back
Top