Relationships

[MajP]

Here is horse data

horse_name     RVALENTINA
owner_name     Relentless Racing, Inc.
program     1
pp     1
todays_cls     78
avg_spd_sd     43
ave_cl_sd     83
hi_spd_sd     43
foal_date     20190508
pstyerl     2.9
pstymid     4.3
pstyfin     5.7
pstynum     1
pstyoff     0
psprstyerl     2.9
psprstymid     4.3
psprstyfin     5.7
psprstynum     1
psprstyoff     0
prtestyerl     0
prtestymid     0
prtestyfin     0
prtestynum     0
prtestyoff     0
pallstyerl     2.9
pallstymid     4.3
pallstyfin     5.7
pallstynum     1
pallstyoff     0
pfigerl     65
pfigmid     56
pfigfin     43
pfignum     1
pfigoff     0
psprfigerl     65
psprfigmid     56
psprfigfin     43
psprfignum     1
psprfigoff     0
prtefigerl     0
prtefigmid     0
prtefigfin     0
prtefignum     0
prtefigoff     0
pallfigerl     65
pallfigmid     56
pallfigfin     43
pallfignum     1
pallfigoff     0
tmmark     
av_pur_val     0.6
weight     120
wght_shift     0
cldate     
price     0
bought_fr     
sex     F
power     43.7
med     
equip     
morn_odds     30/1
breeder     Nicholas Strong, James & Kristine C
wh_foaled     KY
ae_flag     
color     B
power_symb     L
horse_comm     [+]Most horses improve second time out after their first horse racing experience. [-]Rachael Keithan hasn't been victorious when putting Jose Baez up aboard. [-]Didn't finish near the top of the field last race - might have to expect the same today.
breed_type     TB
lst_salena     FTK OCT YRLG 2020
lst_salepr     1000
lst_saleda     20201026
claimprice     0
avgspd     43
avgcls     83
apprweight     0
axciskey     049058050052053053062061

The PP data shows past performance for a given horse. Anyone see a field that relates the two?

 

[mike60smart]

Program & Race Number ?

 

[MajP]

Program & Race Number ?

Unfortunately, those are repeating numbers that go between 1 and 12 and 1 and 9. There are 62 horses in horse data and 1074 PP data.

 

[Tyrone]

So here is the past performance data for one record with field name and value

racedate     20220120
trackcode     AQU
trackname     Aqueduct
racenumber     1
racebreed     TB
country     USA
racetype     MCL
raceclass     MC
claimprice     50000
purse     48000
classratin     83
trackcondi     SY
distance     700
disttype     F
aboutdist  
courseid     D
surface     D
pulledofft     0
winddirect  
windspeed     0
trackvaria     -3
sealedtrac     Y
racegrade     0
agerestric     03
sexrestric     F
statebredr  
abbrevcond  
postpositi     4
favorite     0
weightcarr     120
jockfirst     Jose
jockmiddle  
jocklast     Baez
jocksuffix  
jockdisp     Baez Jose
equipment     F
medication  
fieldsize     6
posttimeod     12.3
shortcomme     PINCH BTW 7/16, WKND
longcommen     brk in step st, prompted 4w betw, pinched 7/16, 2w 1/4p, wknd
gatebreak     2
position1     2
lenback1     50
horsetime1     24.07
leadertime     23.99
pacefigure     65
position2     3
lenback2     200
horsetime2     48.55
leadertim2     48.22
pacefigur2     56
positionst     4
lenbackstr     560
horsetimes     75.56
leadertim3     74.63
dqindicato  
positionfi     4
lenbackfin     925
horsetimef     90.15
leadertim4     88.61
speedfigur     43
turffigure     0
winnersspe     61
foreignspe     -97
horseclaim     0
biasstyle     F
biaspath     N
complineho     Sweet Surprise
complinele     75
complinewe     120
complinedq  
complineh2     Solid Tune
complinel2     375
complinew2     120
complined2  
complineh3     Afilada
complinel3     475
complinew3     120
complined3  
linebefore  
lineafter  
domesticpp     1
oflfinish     4
runup_dist     50
rail_dist     -1
apprweight     0
vd_claim  

[/QUOTE]
[QUOTE="MajP, post: 1818460, member: 146445"]
I am still trying to see if I can crack this code if one exists.
The ppData table I assume is a bunch of different races for a bunch of different horses not the results of 1 horse.  I assume 1 horse cannot run 1074 races.

So how do you find which particular horse that row relates to?
Query3 [B]Query3[/B]

[TABLE]
[TR]
[TH]racedate[/TH]
[TH]position1[/TH]
[TH]position2[/TH]
[TH]positionfi[/TH]
[/TR]
[TR]
[TD][RIGHT]20220120[/RIGHT][/TD]

[TD][RIGHT]2[/RIGHT][/TD]

[TD][RIGHT]3[/RIGHT][/TD]

[TD][RIGHT]4[/RIGHT][/TD]
[/TR]
[/TABLE]
What is position 1 and what is positionfi?
[/QUOTE]
Position1:  means when the horses leaves the starting gate.  Positionfi:  means the final Position the horses finish the race.

 

[Tyrone]

I am still trying to see if I can crack this code if one exists.
The ppData table I assume is a bunch of different races for a bunch of different horses not the results of 1 horse. I assume 1 horse cannot run 1074 races.

So how do you find which particular horse that row relates to?
Query3 Query3

racedate	position1	position2	positionfi
20220120​	2​	3​	4​

What is position 1 and what is positionfi?

Position1: means the position the horse's in when leaving the starting gate. Positionfi: Is the position a horse finish the race.

 

[MajP]

Position1: means the position the horse's in when leaving the starting gate. Positionfi: Is the position a horse finish the race.

Great. But what horse does that record represent? It is meaningless data if you have a lot of information about a horse's race, but no way to determine what horse the data relates to.

 

[Tyrone]

Great. But what horse does that record represent? It is meaningless data if you have a lot of information about a horse's race, but no way to determine what horse the data

Great. But what horse does that record represent? It is meaningless data if you have a lot of information about a horse's race, but no way to determine what horse the data relates to.

Here's a link on how to read PP's. This is what I am trying to create and read. There's lots of informationin there about a horse in that PP's.

 

[MajP]

Your post is of no value. Please look at table ppData. Pick any row (record) and tell me what horse that data belongs to. Do you know. I know all types of things about the race for that horse. I know the jockey, positions, weather, track, purse, claim price, times. But what I do not see anywhere is the horses name or a key to that horse. So the information is good but there is no way to see what horse the information belongs to. If you see it please tell me.
You cannot join tables if there is not common information between tables. In fact non of the tables appear to be linked. But at minimum if you could like ppData and horseData you would at least have something useful. Heck, even if you can decipher which horse the ppdata records belong to you would have something.
Any chance this is sample data for free? They then provide you the needed fields once you pay up?

 

[Tyrone]

All the information of the horses that ran on that days card, are in those 9 tables. Each horse has its own set of data in all of those 9 tables.
The issue is that there are 5 of these tables that don't have any similar fields to join to.

 

[Tyrone]

Your post is of no value. Please look at table ppData. Pick any row (record) and tell me what horse that data belongs to. Do you know. I know all types of things about the race for that horse. I know the jockey, positions, weather, track, purse, claim price, times. But what I do not see anywhere is the horses name or a key to that horse. So the information is good but there is no way to see what horse the information belongs to. If you see it please tell me.
You cannot join tables if there is not common information between tables. In fact non of the tables appear to be linked. But at minimum if you could like ppData and horseData you would at least have something useful. Heck, even if you can decipher which horse the ppdata records belong to you would have something.
Any chance this is sample data for free? They then provide you the needed fields once you pay up?

I will contact Trackmaster.com and ask a few questions about purchasing data.

 

[MajP]

I will contact Trackmaster.com and ask a few questions about purchasing data.

Looks like they have a complete menu of different types of data available. Looks like it can get pretty pricey, but since it is your business, might as well get good data.

 

[Tyrone]

Looks like they have a complete menu of different types of data available. Looks like it can get pretty pricey, but since it is your business, might as well get good data.

I call today and spoke to Greg Robinson @ 650-265-1409. I ask about the primary's keys in the tables. He said that the race number is connected to the horse number and the other tables connect to the horse number, and that others figured it out for their databases.

 

[MajP]

He said that the race number is connected to the horse number and the other tables connect to the horse number,

These questions are not technical database questions, but a game of Where is Waldo. And so far no one can find Waldo
That would be fantastic if that was true. But since there is not a horse number and no race number then that makes NO sense. None of the other tables are related by horse number since there is no horse number and none of those tables reference a horse no.. I like to help but this data set is unusable.

 

[jdraw]

I'm not an xml person, but I looked at the data (xml) in the zip in post #10. I saw the track, the race date,race number and then went to google to find related/corroborating info. I found this for race1 aqueduct feb6/22. The <horsedata> shows the horses in that race. There are various stats, jockey etc . Someone with xml familiarity may decipher more info. I agree that without a map to identify the meaning of the "field names" it could be a long journey to put this data into a user-friendly database format.

From the XML
<racedata>
<race>1</race>
<todays_cls>78</todays_cls>
<distance>700.0</distance>
<dist_unit>F</dist_unit>
<course_id>D</course_id>
<surface>D</surface>
<stkorclm>MS</stkorclm>
<purse>80000</purse>
<claimamt>0</claimamt>
<age_restr>03</age_restr>
<bet_opt>Exacta, Trifecta (.50), Super (.10), Pick 3 Races (1-3) Early Pick 5 (.50) Races (1-5), Double Wagers</bet_opt>
<track>AQU</track>
<send_track>AQU</send_track>
<raceord>1</raceord>
<partim>127.52</partim>
<dist_disp>7F</dist_disp>
<breed_type>TB</breed_type>
<post_time>12:50PM</post_time>
<race_date>20220206</race_date>
<race_text><![CDATA[Trk Rcrd: (1:20.04) (UP TO $13,920 NYSBFOA) FOR MAIDENS, FILLIES THREE YEARS OLD. Weight, 120 lbs. (Non-Starters For A Claiming Price Of $40,000 Or Less In The Last 3 Starts Preferred).]]></race_text>
<stk_clm_md>MDN</stk_clm_md>
<country>USA</country>
<horsedata>
<horse_name>RVALENTINA</horse_name>
<owner_name>Relentless Racing, Inc.</owner_name>
<program>1</program>
.....

 

[Tyrone]

These questions are not technical database questions, but a game of Where is Waldo. And so far no one can find Waldo
That would be fantastic if that was true. But since there is not a horse number and no race number then that makes NO sense. None of the other tables are related by horse number since there is no horse number and none of those tables reference a horse no.. I like to help but this data set is unusable.

I am so grateful and appreciate all the help the Forum (you in particular) have provided, towards my project. MS Access is totally new to me. Again, it's only 3 months since I first started this project and whatever I have learn (not much) came from just watching Youtube videos. So please bear with me if the terminology and understanding isn't there yet, but I will not quit on this project. Am asking you in a polite manner, If it would be possible for you to make a connection with Mr. Greg Robinson @ 650-265-1409 (office hrs. 8am - 8pm pacific time) and ask him a few knowledgeable question, which will give you a better understanding about these Tables and how they are related.
I will find Waldo some how or the other.
Pleased and thank you.

 

[GPGeorge]

I am so grateful and appreciate all the help the Forum (you in particular) have provided, towards my project. MS Access is totally new to me. Again, it's only 3 months since I first started this project and whatever I have learn (not much) came from just watching Youtube videos. So please bear with me if the terminology and understanding isn't there yet, but I will not quit on this project. Am asking you in a polite manner, If it would be possible for you to make a connection with Mr. Greg Robinson @ 650-265-1409 (office hrs. 8am - 8pm pacific time) and ask him a few knowledgeable question, which will give you a better understanding about these Tables and how they are related.
I will find Waldo some how or the other.
Pleased and thank you.

Unfortunately, this is NOT an MS Access problem.

It is in the data itself. This is not relational data. It is a set of lists.

I have only followed the discussion without trying to take more than one initial look at the data to confirm that it is useless as reported by others who did the deep dive into it. If they can't find any fields in the various tables to relate one to another, it can't be used in a relational database application regardless of whether you use Access, SQL Server, Oracle, MySQL, PostgresSQL, etc. The relational database application, or database engine is immaterial. It's the data.

Now you want someone to invest their own time and expertise doing some leg work for you by interviewing a third party. Sorry, but I doubt that is going to happen either. YOU could go back to that source and ask those questions, could you not? Explain why it's not usable for your purposes and ask for a different version. Or point him here and ask him to read this discussion and explain how to use his data? Someone suggested that perhaps the free data is free because the provider knows it can't be used without additional information that he can sell to you. A lot of "free" software works that way. You get the taster version for free, but have to pay for the additional features to make it usable.

 

[Tyrone]

Unfortunately, this is NOT an MS Access problem.

It is in the data itself. This is not relational data. It is a set of lists.

I have only followed the discussion without trying to take more than one initial look at the data to confirm that it is useless as reported by others who did the deep dive into it. If they can't find any fields in the various tables to relate one to another, it can't be used in a relational database application regardless of whether you use Access, SQL Server, Oracle, MySQL, PostgresSQL, etc. The relational database application, or database engine is immaterial. It's the data.

Now you want someone to invest their own time and expertise doing some leg work for you by interviewing a third party. Sorry, but I doubt that is going to happen either. YOU could go back to that source and ask those questions, could you not? Explain why it's not usable for your purposes and ask for a different version. Or point him here and ask him to read this discussion and explain how to use his data? Someone suggested that perhaps the free data is free because the provider knows it can't be used without additional information that he can sell to you. A lot of "free" software works that way. You get the taster version for free, but have to pay for the additional features to make it usable.

thank you George.

 

[MajP]

From where did you get the XML data? Download from a website, purchased from a vendor, emailed from friend, etc.? This may help us understand the quality of the data, and what to expect.
If you purchased or received from Greg Robinson then he or one of his employees should be able to answer the questions. Ask them to simply explain what are the names of the fields in each table that relate to the other tables for each relation. This cannot be a generic horse number or race number, because no such fields exist. You need the exact names. I am pretty sure they will not be able to answer these questions because this data is incomplete or something is lost in the download process.
FYI, Greg may not appreciate his office phone on this site which can get scraped. You can go back and remove that.

If you get a good set of data we can help you build a great database that can help you do all kinds of data analysis efficiently. But we cannot make random lists into a workable database.

 

[GPGeorge]

FYI, Greg may not appreciate his office phone on this site which can get scraped. You can go back and remove that.

On the site where I am an administrator that phone number would already be masked or removed. Just saying....

 

[Tyrone]

From where did you get the XML data? Download from a website, purchased from a vendor, emailed from friend, etc.? This may help us understand the quality of the data, and what to expect.
If you purchased or received from Greg Robinson then he or one of his employees should be able to answer the questions. Ask them to simply explain what are the names of the fields in each table that relate to the other tables for each relation. This cannot be a generic horse number or race number, because no such fields exist. You need the exact names. I am pretty sure they will not be able to answer these questions because this data is incomplete or something is lost in the download process.
FYI, Greg may not appreciate his office phone on this site which can get scraped. You can go back and remove that.

If you get a good set of data we can help you build a great database that can help you do all kinds of data analysis efficiently. But we cannot make random lists into a workable database.

The XML flies was purchased from Trackmaster.com. Greg didn't sound helpful at all, and said to me other guys figured it out.

I am so grateful and appreciate all the help the Forum (you in particular) have provided, towards my project. MS Access is totally new to me. Again, it's only 3 months since I first started this project and whatever I have learn (not much) came from just watching Youtube videos. So please bear with me if the terminology and understanding isn't there yet, but I will not quit on this project.
I will find Waldo some how or the other.
Pleased and thank you.