Relationships

[Marie-Lynn]

I am confused by table relationships in Access, and the relationships feature
seems to be a very inportant tool. Can anyone please suggest a good online
resource for learning about them?

 

[John W. Vinson]

I am confused by table relationships in Access, and the relationships feature
seems to be a very inportant tool. Can anyone please suggest a good online
resource for learning about them?

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

Crystal's and Allen's tutorials in particular should give you a good intro.

 

[Tom Wickerath]

Hi Marie,

Here is a Microsoft KB (Knowledge Base) article on this topic:

Defining relationships between tables
http://support.microsoft.com/?id=304467

Some key points to remember:

1.) The three types of relationships that you can create include:
One-to-one (1:1)
One-to-many (1:M) and
Many-to-many (M:N)

Of these, the 1:1 relationship is the least common, and a 1:M relationship
is the most common.

A M:N relationship is a special relationship created using two 1:M
relationships, with a join table. This type of relationship includes a
minimum of three tables. Other names for a join table include linking table
or intersection table. The join table *must* have the many (foreign key) side
of the two 1:M relationships.

2.) Pretty much every table should have a primary key defined. A primary key
is a unique identifier--it cannot be null and it cannot have any repeating
values.

3.) A foreign key is a field in a many-side table that has the same data
type and field size as a primary key. The only difference is that it should
NOT be uniquely indexed, unless you are trying to create a one-to-one (1:1)
relationship. The data in a foreign key field is allowed to repeat any number
of times. It can also be null (unknown), unless you specifically set the
required value for the field. Examples of foreign keys would include
StateCode in a Customers table, or CustomerID in an Orders table.

4.) Relationships should be created between the primary key of one table (or
a uniquely indexed field [ie. Indexed: Yes No Duplicates]) in the parent
table and a foreign key in the child ('many-side') table. The same data type
and field size should be used for both fields. Thus, if you have a Text field
with Field Size = 2 set as primary key in one table (state abbreviations, for
example), you should use a Text field of the same Field Size in the related
'many-side' table as the foreign key. If you use an Autonumber as the primary
key in one table, then use a Number / Long Integer field size in the
many-side table. I recommend always removing the default value of zero on any
numeric fields that are used as foreign keys.

5.) You may want to adopt a naming convention for your fields to help keep
it sorted out better. Most people do not use such naming conventions, but I
personally like to do this. I use lowercase "pk" prefix for my Primary Keys
and a lowercase "fk" prefix for my foreign keys. One benefit is that you can
easily scan a relationships view to ensure that all foreign key fields
desinated with the "fk" prefix are involved in a relationship (ie. that you
haven't forgotten to create a relationship when you clearly intended it to be
a foreign key). Other benefits include not having to specify which table in
a query, when you add a key field (primary or foreign) in a query, and later
on, when you are writing VBA code that involves key fields, you will know by
the name of the referenced field if you are dealing with a primary key or a
foreign key.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[ו×די]

I am confused by table relationships in Access, and the relationships
feature
seems to be a very inportant tool. Can anyone please suggest a good
online
resource for learning about them?

 

[Aria]

Wow, what an excellent explanation; very patient and clear. You helped me too!
--
Aria W.

Hi Marie,

Here is a Microsoft KB (Knowledge Base) article on this topic:

Defining relationships between tables
http://support.microsoft.com/?id=304467

Some key points to remember:

1.) The three types of relationships that you can create include:
One-to-one (1:1)
One-to-many (1:M) and
Many-to-many (M:N)

Of these, the 1:1 relationship is the least common, and a 1:M relationship
is the most common.

A M:N relationship is a special relationship created using two 1:M
relationships, with a join table. This type of relationship includes a
minimum of three tables. Other names for a join table include linking table
or intersection table. The join table *must* have the many (foreign key) side
of the two 1:M relationships.

2.) Pretty much every table should have a primary key defined. A primary key
is a unique identifier--it cannot be null and it cannot have any repeating
values.

3.) A foreign key is a field in a many-side table that has the same data
type and field size as a primary key. The only difference is that it should
NOT be uniquely indexed, unless you are trying to create a one-to-one (1:1)
relationship. The data in a foreign key field is allowed to repeat any number
of times. It can also be null (unknown), unless you specifically set the
required value for the field. Examples of foreign keys would include
StateCode in a Customers table, or CustomerID in an Orders table.

4.) Relationships should be created between the primary key of one table (or
a uniquely indexed field [ie. Indexed: Yes No Duplicates]) in the parent
table and a foreign key in the child ('many-side') table. The same data type
and field size should be used for both fields. Thus, if you have a Text field
with Field Size = 2 set as primary key in one table (state abbreviations, for
example), you should use a Text field of the same Field Size in the related
'many-side' table as the foreign key. If you use an Autonumber as the primary
key in one table, then use a Number / Long Integer field size in the
many-side table. I recommend always removing the default value of zero on any
numeric fields that are used as foreign keys.

5.) You may want to adopt a naming convention for your fields to help keep
it sorted out better. Most people do not use such naming conventions, but I
personally like to do this. I use lowercase "pk" prefix for my Primary Keys
and a lowercase "fk" prefix for my foreign keys. One benefit is that you can
easily scan a relationships view to ensure that all foreign key fields
desinated with the "fk" prefix are involved in a relationship (ie. that you
haven't forgotten to create a relationship when you clearly intended it to be
a foreign key). Other benefits include not having to specify which table in
a query, when you add a key field (primary or foreign) in a query, and later
on, when you are writing VBA code that involves key fields, you will know by
the name of the referenced field if you are dealing with a primary key or a
foreign key.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I am confused by table relationships in Access, and the relationships feature
seems to be a very inportant tool. Can anyone please suggest a good online
resource for learning about them?

 

[Tom Wickerath]

Hi Aria,

You're very welcome!

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________