Coach needs help

[Guest]

Hi there, I am a coach for a little man football team. The season is about to
start in a few weeks and the kids were wondering if I could keep track of
their stats this year. I am a beginner at Access and could use some help with
my relationships.

Because it is a little man team, everyone plays both ways and on special
teams. Therefore, a kid will have offensive, defensive, and special teams
stats.

I have many tables but for my question I will only use 3 tables.

Table 1- "Players"
Field 1- Entry
Field 2- Name (PK)

Table 2- "Passing" (No PK Yet)
Field 1- Entry
Field 2- Name
Field 3 thru ? -Stat Categories

Table 3- "Rushing" (No PK Yet)
Field 1-Entry
Field 2-Name
Field 3 thru ?-Stat Categories

My problem is, I need to find a way to make a relationship between Table 1
and Tables 2 and 3. When I define the relationship right now (1 to Many)
between the "Name" fields in the 3 tables, when I click on the (+) sign next
to the name it asks me to choose a table and gives me table 1 and 2. That
would be fine to prompt me every time for that choice but once I decide on a
table initially, it always brings up the info for that table.

I hope I'm not creating confusion... I really appreciaate all the help I can
get!

Thanks,

 

[Guest]

That is how relations work.
Your table1 would have the players name and personnal information - height,
weight, etc. The other tables are related like parent to childern.

If you were tracking the players weight then a different table would have
name or identification, date, weight. The secong table has to have a field
that contains identical information so it can be related. They can be linked
by the name field or identification number.

You have a field call Entry. What is the purpose of the field?

 

[Guest]

The entry field is just an ID field for each record. It really serves no
purpose but I thought you had to have an ID field for every table that you
created. ??

Note that the field that is the same in each table is "Name". (That will be
the link between the tables)

The problem is when I try to link from the "Players" table to any of the
statistical tables, Access prompts me to choose a table. In a sense to decide
which table I am linking to. Once I make my initial decision, it will not
allow me to link to any other table aside from the one I have already linked
to. I was just wondering how one would link one table to multiple tables.
Maybe my diagram will help...

[Table 1]------->[Table 2] (1 to many)
\_____>[Table 3] (1 to many)

Thanks!

 

[Guest]

No you do not have to have an ID field for every table that you created. You
can use the name field. Way back when disk memory was a premium they used
an ID which would be less bytes than a name or other such information.

On the menu click TOOL - Relationships.
Select the tables. They primary key (PK) field of the table will be bold.
Click the PK and drag to the field of the other table in the relation.
NOTE -- the field names do not have to match but the data type must.
Set Enforce Referential Integrity and Cascade Update Related fields.

The entry field is just an ID field for each record. It really serves no
purpose but I thought you had to have an ID field for every table that you
created. ??

Note that the field that is the same in each table is "Name". (That will be
the link between the tables)

The problem is when I try to link from the "Players" table to any of the
statistical tables, Access prompts me to choose a table. In a sense to decide
which table I am linking to. Once I make my initial decision, it will not
allow me to link to any other table aside from the one I have already linked
to. I was just wondering how one would link one table to multiple tables.
Maybe my diagram will help...

[Table 1]------->[Table 2] (1 to many)
\_____>[Table 3] (1 to many)

Thanks!

That is how relations work.
Your table1 would have the players name and personnal information - height,
weight, etc. The other tables are related like parent to childern.

If you were tracking the players weight then a different table would have
name or identification, date, weight. The secong table has to have a field
that contains identical information so it can be related. They can be linked
by the name field or identification number.

You have a field call Entry. What is the purpose of the field?