Relationships

[Tyrone]

I am a newbie to Access. I am trying to build a database for my personal use. I have downloaded a large xml file from a trusted site. There were 9 Tables from that xml file. Next, I try to create a relationship with these 9 tables and I have notice that these 9 tables don't have any Primary Keys and 5 of these tables have matching Fields. The other 4 tables without any matching fields has important data which I'll need for the queries. Could someone please tell me what should be done in order for this database to performance properly.

 

[GPGeorge]

Since we can't see anything on your computer, it's really hard to guess what those 9 tables are and what the fields in them might be.

On the other hand, if you were to provide that kind of information, we could probably apply our experience to helping you clean up and use them..

One way to do that is upload a copy of what you have. Another, less useful, would be screenshots of the tables in design view.

 

[GPGeorge]

Oh, and a description of what "personal use" you plan for this database application? Tracking socks in a sock drawer, or books in a library? Income and Expenses? Field trips? Gosh, I can think of lots of potential things for a relational database application.

 

[jdraw]

I agree with George's comments. You may have 9 tables, but what is the subject of your proposed database? What do those tables represent? What are the major processes/activities of "your personal database"? How do these 9 tables relate to those processes/activities?
If you could provide a plain English description of your "proposed system", independent of the tables you have downloaded, that may get you more focused advice more quickly.
Perhaps you could provide "a walk though my proposed application" starting at the 30,000 foot level and adding more detail if/where necessary.

Good luck and welcome to the forum.

 

[theDBguy]

Hi. Welcome to AWF!

Is there any way you could share a link for downloading the same xml file, so we can give it a try?

 

[MajP]

Make sure to read this article closely.

Relationships1

This article discusses the differences between query joins and table relationships and explains the important use of table relationships to enforce referential integrity. Three methods of adding relationships are discussed. The article also explains how Access uses the MSysRelationships system...

isladogs.co.uk

 

[The_Doc_Man]

Welcome to the forum.

The problem with your question is that it can go any one of a dozen different directions.

MajP suggested you read up on Relationships. I cannot disagree, but would add that you also need to understand Database Normalization. The two topics (his and mine) go together like hand-in-glove.

You have this mess of data and want to know what to do with it. We are all going to have to make "topic" suggestions because we will NEVER know what YOU want to do. (My mind-reader turban didn't survive the dry-cleaners.)

The correct path here is to study the suggested topics and then make some decisions about your intended functionality. You can of course bang right into trying to code something, but the best results on any new project involve forcing yourself to hang back and think. My late father-in-law was a carpenter. He always gave the advice "Measure twice, cut once." I would give the programmer's equivalent as "Analyze thrice, design twice, implement once."

 

[mike60smart]

Welcome to the forum.

The problem with your question is that it can go any one of a dozen different directions.

MajP suggested you read up on Relationships. I cannot disagree, but would add that you also need to understand Database Normalization. The two topics (his and mine) go together like hand-in-glove.

You have this mess of data and want to know what to do with it. We are all going to have to make "topic" suggestions because we will NEVER know what YOU want to do. (My mind-reader turban didn't survive the dry-cleaners.)

The correct path here is to study the suggested topics and then make some decisions about your intended functionality. You can of course bang right into trying to code something, but the best results on any new project involve forcing yourself to hang back and think. My late father-in-law was a carpenter. He always gave the advice "Measure twice, cut once." I would give the programmer's equivalent as "Analyze thrice, design twice, implement once."

Hi Everyone

This has been cross posted https://www.accessforums.net/showthread.php?t=85437&highlight=tyrone

The screenshot of the Relationship Diagram attached

 

[GPGeorge]

Hi Everyone

This has been cross posted https://www.accessforums.net/showthread.php?t=85437&highlight=tyrone

The screenshot of the Relationship Diagram attached

How unfortunate.

On the other hand, I made a decent living for many years rescuing "databases" like that, so in some sense, it is a good thing™, potentially, for someone.

 

[Tyrone]

Since we can't see anything on your computer, it's really hard to guess what those 9 tables are and what the fields in them might be.

On the other hand, if you were to provide that kind of information, we could probably apply our experience to helping you clean up and use them..

One way to do that is upload a copy of what you have. Another, less useful, would be screenshots of the tables in design view.

 

[Tyrone]

Oh, and a description of what "personal use" you plan for this database application? Tracking socks in a sock drawer, or books in a library? Income and Expenses? Field trips? Gosh, I can think of lots of potential things for a relational database application.

Am 61 yrs old. Worked in the Horse racing industry for 30 plus yr. Worked as a Jockey, Exercise Rider, Hot Walker, Entry Clerk, Horse identifier, Placing Judge, Assistant Clerk of Scale and so on. Now that I retired and decided to be a Jockeys Agent I wanted to build my own database for horses that are currently racing in my city. In this database I would like to be able to look up horses by their Name, Age, Sex, Stats, Owner, Trainer, Jockey etc. etc. etc. Here's an example of some of the information on 3 horses. and I think that these information are very handy for the job as an Agent.

 

[The_Doc_Man]

Where you need to study a bit includes understanding that a table represents a "thing" or "action" but that one table can track many attributes associated with that thing. So if you have a horse table, you would have a horse unique ID - which I can't help you with as I refuse to bet on anything that weighs more than I do. But the point is, the horse has a description, an owner, a sex, etc. But then the horse has raced and the separate races make up a related table that is the horse's racing history. (Hence the earlier discussion on Relationships and Normalization). Jockeys would have their own table, of course. I'm seeing potential for at least three tables here - Jockeys, Horses, and Races. The races would act as what is called a JUNCTION TABLE between Jockeys and Horses. And also a junction for info about the Tracks where the races occurred. You will understand that if you read up on normalization and relationships.

In Access, it is all about representation. The tables represent something to do with your business whether it is a person, place, thing, or event.

 

[Tyrone]

I'll get to work on reading normalization and relationships. Thank you

 

[Pat Hartman]

We can't see all the column names in the layouts you posted. If you post the database with those tables loaded, we can see if we can figure out what columns relate the tables. Unless XML removes the foreign keys for some reason.

 

[MajP]

To make this easier I imported in Access. If all the needed data is there, but the tables are not properly normalized that would be fixable. You may have to create new tables and do sql inserts to populate with the old data. However, I looked at this and I am thinking somehow the necessary fields are lost.

Examples
1. You have two tables Dam and Sire which I would assume these are the parents of a given horse. In neither of these tables are a key to the child. This type of "parental" data is normally done in a self referencing table and that can be a little tricky. I cannot figure out what those tables show
Dam has three fields Breed, DamName, DamSire. I do not understand what this shows. Is DamSire the Sire of the Dam or is their a missing field.
Normally I would have a tbl like Horses
HorseID
HorseName
SireID
DamID

That DamID and SireID relate to a Horse ID in the same table. That is in the case where it is likely you maintain parent information in your table. This may not be your case. You may have the detailed information on a horse but only the names of parents.

2. There is a table of workout data. I assume that is a horses workout data? There is no information about a horse so how do you interpret that information?
3. There is a trainer table with a trainer key. I did not know if that key is a primary key, but I would expect in horseData to have some information on the trainer. I can not find the trainer key in the horsedata or even the trainer name. I can find the owner and breeder names.

The ppData needs to be normalized. Wherever you see the same groups of fields but numbered that is a hint that those columns need to be turned into rows in a child table.
So you see
ppdata ppdata

position1	lenback1	horsetime1	leadertime	pacefigure
2​	50​	24.07​	23.99​	65​

Then this same data repeated
ppdata ppdata

position2	lenback2	horsetime2	leadertim2	pacefigur2
3​	200​	48.55​	48.22​	56​

This data goes into a child table and these become rows. However, I do not really understand what this represents. Position 1 has a value of 2 which is not a horse ID. I would think this data is showing information about each horse in each position?

What is your goal? Are you trying to use the above data in a database or build a database that captures similar types of information. Either way I think you are better off building a clean set of tables from scratch. I would not try to save and chop this. Even if this is the source where you get information. You can periodically download this XML and create a set of insert queries to update you database with the current information. This would likely be the same set of queries you use the first time to move this information into a properly normalized database.

 

[Tyrone]

To make this easier I imported in Access. If all the needed data is there, but the tables are not properly normalized that would be fixable. You may have to create new tables and do sql inserts to populate with the old data. However, I looked at this and I am thinking somehow the necessary fields are lost.

Examples
1. You have two tables Dam and Sire which I would assume these are the parents of a given horse. In neither of these tables are a key to the child. This type of "parental" data is normally done in a self referencing table and that can be a little tricky. I cannot figure out what those tables show
Dam has three fields Breed, DamName, DamSire. I do not understand what this shows. Is DamSire the Sire of the Dam or is their a missing field.
Normally I would have a tbl like Horses
HorseID
HorseName
SireID
DamID

That DamID and SireID relate to a Horse ID in the same table. That is in the case where it is likely you maintain parent information in your table. This may not be your case. You may have the detailed information on a horse but only the names of parents.

2. There is a table of workout data. I assume that is a horses workout data? There is no information about a horse so how do you interpret that information?
3. There is a trainer table with a trainer key. I did not know if that key is a primary key, but I would expect in horseData to have some information on the trainer. I can not find the trainer key in the horsedata or even the trainer name. I can find the owner and breeder names.

The ppData needs to be normalized. Wherever you see the same groups of fields but numbered that is a hint that those columns need to be turned into rows in a child table.
So you see
ppdata ppdata

position1	lenback1	horsetime1	leadertime	pacefigure
2​	50​	24.07​	23.99​	65​

Then this same data repeated
ppdata ppdata

position2	lenback2	horsetime2	leadertim2	pacefigur2
3​	200​	48.55​	48.22​	56​

This data goes into a child table and these become rows. However, I do not really understand what this represents. Position 1 has a value of 2 which is not a horse ID. I would think this data is showing information about each horse in each position?

What is your goal? Are you trying to use the above data in a database or build a database that captures similar types of information. Either way I think you are better off building a clean set of tables from scratch. I would not try to save and chop this. Even if this is the source where you get information. You can periodically download this XML and create a set of insert queries to update you database with the current information. This would likely be the same set of queries you use the first time to move this information into a properly normalized database.

Appreciate you spending your time looking at my post. All the data are there for me to work with. If I can first know how to join these tables.
1. The Dam and the Sire are the parent of the given horse. The DamName is the Dam and the DameSire is the Sire of the Dam. And yes the detail information is on the horse and both the Dam and Sire are just the parents so there is no missing fields there.
2. The Workout table is information of the horse workout.
3. Trainer tbl. Every horse that's racing must have a trainer, which let us know who's training the horse and the Trainers sometimes trains more than 1 horse. And same goes for an Owner.
The value 2 in Position 1 let's you know what position that particular horse is in during the running of the race.
As an Agent, my goal is to use these information, to try and choose the best possible horses for my Jockey to ride in a race.
In the attachment below is a PDF file of a Racing Form showing the past performance on horses. Past performance or PP's for short, comes out at the end of every race day. There are different race entries drawn to form a race card, Before Trainers enter their horse or horses for a race, they have to get a Jockey to ride these thoroughbred. And Agent and their Jockeys tries to find the best possible horse to ride. So here's where my job as an Agent comes in. By downloading the xml files into a database and running different criteria will give me an advantage by having all the necessary information for choosing the best possible horse for my Jockey to rider..
So Please let me know your views. I would loves to get this database rolling.
Thank you for reading.

 

[MajP]

I interpret this as you want to be able to import this actual data, and then be able to build reports and queries from it. You will import this data periodically. You do not plan to add, modify the data.
What you plan to do is important to how much work you put in. If I was doing heavy data analysis I would be cleaning it up a lot, if I am just doing queries and reports then I am going to work with the current structure the best I can.

The problem I have I that I do not see how these tables relate. I get what you are saying, but the tables do not support that or are missing something. There may be additional tables or something lost in the export.

You have really two main tables "horseData" which is information about a horse and its performance, and "ppData" which I assume is pole position and has detailed information about a specific race.

These tables are not linked as far as I can tell. I would expect in the ppData that for every horse in ppData would have a link to the horse table.
In the horse table I would expect a horse to be linked to its sire, dam, trainer, owner, breeder.

But as far as I can tell there are no links. Or they have been lost

Examples:
1. Table Dam
dam dam

stat_breed	damname	damsire
TB	Kiss Me Forever	BROKEN VOW

If I understand damName is the name of some horses mother and damsire is the name of Kiss Me Forever sires. Broken Vow h would be the the grandfather of some horse that is the child of Kiss Me Forever. However there is nothing here to complete the relation.

2. Same problem with Sire
sire sire

stat_breed	sirename	tmmark	stud_fee
TB	Point of Entry		7500​

This has information about Point of Entry. Point of Entry is somebodies Sire, but there is no link to determine who POE is the father.

I would expect this relation to be established in the horse data table, but there is nothing.
In horseData a horse should have a key to its Sire and Dam
in horseData there should be a key to its parents, jockey, owner, breeder, etc.

If American Lincoln's mother was Kiss Me Forever, and its Father was Point of entry then in the horsedata table I should see

HorseName: American Lincoln
Horse Dam: Kiss Me Forever
Horse Sire: Point of Entry

So you can pull data from each table, but I do not see any way they are tied together.

 

[MajP]

The value 2 in Position 1 let's you know what position that particular horse is in during the running of the race

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.

The value 2 in Position 1 let's you know what position that particular horse is in during the running of the race\

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?

 

[GPGeorge]

"I am still trying to see if I can crack this code if one exists."

 

[MajP]

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     
vd_reason