Relationships

[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 files was purchase from Trackmaster.com. Greg didn't seem very helpful at all. Said other guys figured it out.

 

[Tyrone]

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

I remove that number as soon as it was mention. I don't want to cause any trouble.

 

[LarryE]

I will admit almost no knowledge of horses or racing but I downloaded your tables and just using a little common sense, came up with the following basic design:

Now, there is a lot wrong with these tables including duplicate data and erroneous Data Types and I renamed them so when you insert them into queries, you know it is a table (just good practice). As far as I can determine with very little knowledge:

1. You have Dams and Sires which may have multiple horses born to them.
2. Each horse has a trainer or trainers which may have multiple training sessions.
3. Each horse also may have multiple jockeys riding them which may ride in multiple races at various racetracks.
4. Each jockey in each race has a race result (TblStat) Note: We usually do not keep statistics in tables. They can be calculated on forms and reports. But the stats table allows you to enter Win, Place, Show and earnings etc. for that race. You can add fields as you wish of course.
5. I have no clue what the TblPlayers is for. It has no relationship at the moment.

Is this correct? I hope this will get you started at least.

 

[Tyrone]

I will admit almost no knowledge of horses or racing but I downloaded your tables and just using a little common sense, came up with the following basic design:
View attachment 99560
Now, there is a lot wrong with these tables including duplicate data and erroneous Data Types and I renamed them so when you insert them into queries, you know it is a table (just good practice). As far as I can determine with very little knowledge:

1. You have Dams and Sires which may have multiple horses born to them.
2. Each horse has a trainer or trainers which may have multiple training sessions.
3. Each horse also may have multiple jockeys riding them which may ride in multiple races at various racetracks.
4. Each jockey in each race has a race result (TblStat) Note: We usually do not keep statistics in tables. They can be calculated on forms and reports. But the stats table allows you to enter Win, Place, Show and earnings etc. for that race. You can add fields as you wish of course.
5. I have no clue what the TblPlayers is for. It has no relationship at the moment.

Is this correct? I hope this will get you started at least.

First of all, I appreciate your interest in looking into my Project and I would like to thank you very much.
Just a few things to note:
1. You were correct about the Dam and Sire.
2. Each horse has a trainer and each trainer may also have multiple horses racing on that day.
3. A jockey can only ride one horse in a race, Jockeys may also ride multiple races on that racetrack on that day.
4. Each horse in a race has a race result in (TblStat). Starts, Win, Place and Show.
5. I have no clue myself where that TblPlayer came from, to my knowledge it has nothing to do with horse racing.

 

[MajP]

I think there is some confusion. @Tyrone is not trying to create a database, but trying to extract the existing data. The primary and foreign keys that @LarryE is providing is a notional way ahead, and would be great if those fields existed. However, not one of those keys actually exist in the data set. We can suggest the proper structure and even build it, but would still have no way to use the data.

 

[LarryE]

I think there is some confusion. @Tyrone is not trying to create a database, but trying to extract the existing data. The primary and foreign keys that @LarryE is providing is a notional way ahead, and would be great if those fields existed. However, not one of those keys actually exist in the data set. We can suggest the proper structure and even build it, but would still have no way to use the data.

I created those fields in the file I attached so he could see how that might work. He somehow got the data converted into ACCESS tables so that was at least a start but the tables were not connected. I suggested a way to do that. Now the real work begins for him.

 

[LarryE]

First of all, I appreciate your interest in looking into my Project and I would like to thank you very much.
Just a few things to note:
1. You were correct about the Dam and Sire.
2. Each horse has a trainer and each trainer may also have multiple horses racing on that day.
3. A jockey can only ride one horse in a race, Jockeys may also ride multiple races on that racetrack on that day.
4. Each horse in a race has a race result in (TblStat). Starts, Win, Place and Show.
5. I have no clue myself where that TblPlayer came from, to my knowledge it has nothing to do with horse racing.

So there should be a link between trainers and races? I thought a trainer could train a horse but that horse doesn't always race at a track. That's why I didn't create a link between trainers and races (TblPPData). I would rename PPData to something like TblRaces, by the way.

You also need to:

1. Go through each table and delete duplicate records.
2. Check each and every field Data Type. Some Text fields should be Currency, for example.
3. Rename fields in each table to be more descriptive of what they actually are.
4. Delete the TblPlayers table.
5. Delete the Combine query. It uses the Players table and is useless.

I don't know which Sires and Dams relate to which horses. That information you will need to get somehow if it is important to you. Each horse needs a Sire and Dam number in its SireID and DamID field. If Sire and Dam information is not important, then delete those two tables and move on.

 

[Tyrone]

So there should be a link between trainers and races? I thought a trainer could train a horse but that horse doesn't always race at a track. That's why I didn't create a link between trainers and races (TblPPData). I would rename PPData to something like TblRaces, by the way.

You also need to:

1. Go through each table and delete duplicate records.
2. Check each and every field Data Type. Some Text fields should be Currency, for example.
3. Rename fields in each table to be more descriptive of what they actually are.
4. Delete the TblPlayers table.
5. Delete the Combine query. It uses the Players table and is useless.

I don't know which Sires and Dams relate to which horses. That information you will need to get somehow if it is important to you. Each horse needs a Sire and Dam number in its SireID and DamID field. If Sire and Dam information is not important, then delete those two tables and move on.

At present, I'm away from my PC for a few days. Luckily I'm able to use my Samsung tab to view and reply to my messages. But will get back to work as soon as I can.

 

[LarryE]

@Tyrone:
Do still need help with this project? How far did you get?

 

[Tyrone]

@Tyrone:
Do still need help with this project? How far did you get?

Yes I still need help, thank you for your interest. I got as far as removing duplicates from 3 tables. The other tables are ok. My next step is putting primary keys into the tables, but yeah I'm still working on my project.

 

[LarryE]

I've been working on this and will get back to you in a couple of days with an updated file. This is a very difficult and complex project. I have worked with ACCESS for more than two decades and I am not even sure this can be done. You can certainly create tables and forms to enter new data and go forward with whatever statistics you wish. Incorporating past historical data is another story. I'll update you further after I get a few things done.

 

[Tyrone]

I've been working on this and will get back to you in a couple of days with an updated file. This is a very difficult and complex project. I have worked with ACCESS for more than two decades and I am not even sure this can be done. You can certainly create tables and forms to enter new data and go forward with whatever statistics you wish. Incorporating past historical data is another story. I'll update you further after I get a few things done.

Question, Can you put all these tables into one table and run different queries.

 

[LarryE]

No. You do not combine tables to create another table because the fields have relationships with other fields in other tables. Those relationships need to be maintained and are sacred. What you do is use the different tables to create queries depending on what you are attempting to accomplish. See the attached file for examples of table relationships and queries.

 

[GPGeorge]

Question, Can you put all these tables into one table and run different queries.

What you propose would be along the lines of a Data Warehouse, as opposed to being an OLTP, or online Transaction Processing database.

If you want or need to create a database in which static tables are used for reporting only, i.e. a Data Warehouse, that is certainly an option. Lots of work, but an option.

 

[LarryE]

@Tyrone:
Here are some other things you should know:

1. Every single field in every table had its 'Required' setting set to 'Yes'. No one would ever be able to enter any new data under those conditions. I re-set every field 'Required' setting to 'No'. That way, you could enter new data at some point.
2. Every Date field was set with a Data Type of Numeric. I converted all of them to actual Dates and set the Data Types to Date/Time so ACCESS can properly use them. The date format I set is mm/dd/yyyy in all date fields. USA format.
3. We know which jockey rode which horse at each racetrack, but we have no information on the results of any race. The table that held race results is useless because no link can be made with any race.
4. There are still dozens of fields in each table that are unknown as to what they are.
5. There are no links that can be made between Sires, Dams and their offspring. This will need to be determined by some other means and then input into the Horses table.
6. There is no link between Trainers and anything else. They cannot be related to any horse or racetrack or individual race. The data simply is not there. You will need to manually add this information for each and every racetrack.

Your job now is to go through every field in every table and determine what that field is, and if you need the information. If you don't need it, then delete the field completely. You can do that by opening the table in Design mode. ACCESS will give you a warning. If you need the field in the table, then change its name so we know what it is.

Make a list of information you need that is not in any of the fields.

 

[Tyrone]

@Tyrone:
Here are some other things you should know:

1. Every single field in every table had its 'Required' setting set to 'Yes'. No one would ever be able to enter any new data under those conditions. I re-set every field 'Required' setting to 'No'. That way, you could enter new data at some point.
2. Every Date field was set with a Data Type of Numeric. I converted all of them to actual Dates and set the Data Types to Date/Time so ACCESS can properly use them. The date format I set is mm/dd/yyyy in all date fields. USA format.
3. We know which jockey rode which horse at each racetrack, but we have no information on the results of any race. The table that held race results is useless because no link can be made with any race.
4. There are still dozens of fields in each table that are unknown as to what they are.
5. There are no links that can be made between Sires, Dams and their offspring. This will need to be determined by some other means and then input into the Horses table.
6. There is no link between Trainers and anything else. They cannot be related to any horse or racetrack or individual race. The data simply is not there. You will need to manually add this information for each and every racetrack.

Your job now is to go through every field in every table and determine what that field is, and if you need the information. If you don't need it, then delete the field completely. You can do that by opening the table in Design mode. ACCESS will give you a warning. If you need the field in the table, then change its name so we know what it is.

Make a list of information you need that is not in any of the fields.

This is the xlm zip file that I downloaded and extracted to using in Access. It has every bit of information in there, for example; there are 56 horse race @ WO on day 20220416. Each horse that race on that day has their own data, I mean everything you need to know about every horse on that day during its race. Plus anywhere between 0 - 10 past performance data of each horse that ran that day. Could you have a look and let me what you think? thank you.

 

[LarryE]

I imported this file into ACCESS and created a query with only one record. The Race on 20220401 (I assume that was 04/01/2022, but cannot tell for sure). Here is some of the field data for that race. I know it was held at Turfway Park and a jockey by the name of Gerardo Corrales rode in at least one race.

Can you tell me:

1. Which horse he rode?
2. How he placed in the race?
3. Which other jockeys raced in that race?
4. How they placed?
5. What is racenumber? Is that the number of races run that day or is that the number of the race that Corrales rode a horse in? So he rode one of the horses in complineho or complineh2 or complineh3 in race #7?

When I created the field connections between jockeys and horses and race dates, I used the complineho field as the horse name that the jockey rode on each date at each racetrack. I don't know if that is correct or not but:

1. Each horse needs to have a jockey assigned to it
2. Each jockey and horse needs to have a racetrack assigned to them on each race day
3. Then there needs to be a race result for each one of those horses, jockeys, racetracks and race dates.

It appears to me that this xml file cannot give you the full information you will need. It only gives partial information. I don't know where you are getting this xml file from, but you cannot build an ACCESS solution without all the information for each race date and racetrack.

For each race track and race day, there should be various jockeys riding their various horses. Each with a result in those races. I don't know for a fact, but there might even be different numbers of races conducted each day at each track. If this is the case, it becomes even more complex and difficult.

 

[Tyrone]

I imported this file into ACCESS and created a query with only one record. The Race on 20220401 (I assume that was 04/01/2022, but cannot tell for sure). Here is some of the field data for that race. I know it was held at Turfway Park and a jockey by the name of Gerardo Corrales rode in at least one race.
View attachment 99996
Can you tell me:

1. Which horse he rode?
2. How he placed in the race?
3. Which other jockeys raced in that race?
4. How they placed?
5. What is racenumber? Is that the number of races run that day or is that the number of the race that Corrales rode a horse in? So he rode one of the horses in complineho or complineh2 or complineh3 in race #7?

When I created the field connections between jockeys and horses and race dates, I used the complineho field as the horse name that the jockey rode on each date at each racetrack. I don't know if that is correct or not but:

1. Each horse needs to have a jockey assigned to it
2. Each jockey and horse needs to have a racetrack assigned to them on each race day
3. Then there needs to be a race result for each one of those horses, jockeys, racetracks and race dates.

It appears to me that this xml file cannot give you the full information you will need. It only gives partial information. I don't know where you are getting this xml file from, but you cannot build an ACCESS solution without all the information for each race date and racetrack.

For each race track and race day, there should be various jockeys riding their various horses. Each with a result in those races. I don't know for a fact, but there might even be different numbers of races conducted each day at each track. If this is the case, it becomes even more complex and difficult.

can we whats app each other? I'll be able to explain things much better to you.

 

[LarryE]

I don't know what that is. Sorry.

 

[Tyrone]

RACEDATE....20220401 is the date that the horse last ran. TRACKNAME...Turfway Park is the race track where the horse last ran. RACENUMBER 7 is the 7th race out of 10 races on that day. JOCKDISP... Jockey Corrales Gerardo rode a horse in race 7 and probably rode other horses in other races, on that same race day at that same Race Track(Turfway Park). And the other complineho, complineh2, complineh3 are the names of the horses that finish 1st, 2nd, and 3rd of that race.

can we WHATS APP each other? I'll be able to explain things much better to you.

I don't know what that is. Sorry.

WHATS APP is a app on a cell phone (Samsung galaxy cell phone). you can communicate with family or friends from all over the globe for free once you have a wi fi connection. you can either do a video or a phone call.