Relationships

[a]

I'm using access 2007

I have this 2 table for (example)

First table

IDPlayer AutoNumber

PlayerName Text

SportType Text

Sex Text

Age number

Second Table

IDClub AutoNamber

Clubname Text

How Can I join this 2 table? Please give me full answer

Example:

1- Is there any field need to add to any of this table? and why?

2- Is there any field need to change data type? and why?

I want learn relationship by example

This 2 table concern of player and the club they play on the club may
contain more player.

Relation ship one to many

One club many players

Please give me the answer step by step this is important for me

Is there any tips welcome

Thank you

 

[boblarson]

What you would actually need is a JUNCTION table (for a many to many
relationship). You include

Table - tblPlayerClubs
tblPlayerClubsID - Autonumber (PK)
IDPlayer - Long Integer (FK)
ID Club - Long Integer (FK)
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

 

[John W. Vinson]

I'm using access 2007

I have this 2 table for (example)

First table

IDPlayer AutoNumber

PlayerName Text

SportType Text

Sex Text

Age number

Second Table

IDClub AutoNamber

Clubname Text

How Can I join this 2 table? Please give me full answer

Example:

1- Is there any field need to add to any of this table? and why?

2- Is there any field need to change data type? and why?

I want learn relationship by example

This 2 table concern of player and the club they play on the club may
contain more player.

Relation ship one to many

One club many players

Please give me the answer step by step this is important for me

Is there any tips welcome

Thank you

If each club consists of many players, but each player can play for only one
club, you need to add a Long Integer field to the first table as a link - a
"foreign key" - to the IDClub field in the second table. You can name it
IDClub, or any name that you choose (Access doesn't check the fieldname); you
should use the Relationships window to define a join between the IDClub field
in Clubs to this field in Players.

For data entry consider using a Form based on Clubs, with a Subform based on
Players, using IDClub as the master/child link field.

For reporting, use a Query joining the two tables.

For some turtorials and suggestions on how to manage databases, see

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials



John W. Vinson [MVP]

 

[a]

thank you for your help and answer

If each club consists of many players, but each player can play for only
one
club, you need to add a Long Integer field to the first table as a link -
a
"foreign key" - to the IDClub field in the second table. You can name it
IDClub, or any name that you choose (Access doesn't check the fieldname);
you
should use the Relationships window to define a join between the IDClub
field
in Clubs to this field in Players.

For data entry consider using a Form based on Clubs, with a Subform based
on
Players, using IDClub as the master/child link field.

For reporting, use a Query joining the two tables.

For some turtorials and suggestions on how to manage databases, see

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials



John W. Vinson [MVP]