• ITS home
• Help
• Services
• My IT account
• Security
• About

Questions and answers

898

What are relationships in Microsoft Access ?

In a relational database (Access), the data in one table is related to the data in other tables. In general, tables can be related in one of three different ways: one-to-one, one-to-many or many-to-many.  The relationship is used to cross reference information between tables.

One to One

In a one-to-one relationship each record in one table has at most one related record in another table.

In a one-to-one relationship, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A. This type of relationship is not common, because most information related in this way would be in one table. You might use a one-to-one relationship to divide a table with many fields, to isolate part of a table for security reasons, or to store information that applies only to a subset of the main table.

One to Many

A one-to-many relationship, often referred to as a "master-detail" or "parent-child" relationship.

A one-to-many relationship is the most common type of relationship. In a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A.

Many to Many

In a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A. This type of relationship is only possible by defining a third table (called a junction table) whose primary key consists of two fields Ã¯Â¿Â½ the foreign keys from both Tables A and B. A many-to-many relationship is really two one-to-many relationships with a third table.

A many-to-many relationship means that for each record in one table there can be many records in another table and for each record in the second table there can be many in the first.

Many-to-many relationships can not be directly represented in relational database programs and have to be built by using two or more one-to-many relationships.

Defining relationships

You define a relationship by adding the tables that you want to relate to the Relationships window, and then dragging the key field from one table and dropping it on the key field in the other table.

The kind of relationship that Microsoft Access creates depends on how the related fields are defined:

• A one-to-many relationship is created if only one of the related fields is a primary key or has a
• A one-to-one relationship is created if both of the related fields are primary keys or have unique indexes.
• A many-to-many relationship is really two one-to-many relationships with a third table whose primary key consists of two fields � the foreign keys from the two other tables.

Note If you drag a field that isn't a primary key and doesn't have a unique index to another field that isn't a primary key and doesn't have a unique index, an indeterminate relationship is created. In queries containing tables with an indeterminate relationship, Microsoft Access displays a default join line between the tables, but referential integrity won't be enforced, and there's no guarantee that records are unique in either table.

When creating a relationship between two tables MS Access provides uses the Referential Integrity feature. This feature prevents adding records to a detail table for which there is no matching record in the master table. It will also cause the key fields in the detail table to be changed when the corresponding key fields in the master are changed - this is commonly referred to as a cascading update. The second option is to enable cascading deletes. This causes the deletion of all the related records in a detail table when the corresponding record in the master table gets deleted.

More Help with relationships can be obtained from:

http://support.microsoft.com/kb/304466#appliesto