DBWebApplications 





Advanced Features  «Prev  Next»
Lesson 2 Reviewing relationships
Objective Identify different types of relationships used in Access.

Different Types of Relationships used in Access

In working with Access, either through your own experience or by taking previous courses on Access, you should have had some contact with the basics of Access relationships. Access relationships allow you to tell Access how you want pieces of information to “relate” to one another.
Types of relationships
There are three types of relationships in Access:
1) One-to-many:
The most common type of relationship, this represents having one record in a table relating to many records in another table. A good example of this is in the project database, Consulting . In the project database, one client can have many projects. They are related on the ClientNo field. You can see this relationship in the following figure:
One record in a table relating to many records in another table
One record in a table relating to many records in another table
2) Many-to-many:
This relationship is used to define when many records in one table can relate to many records in another table. An example of a many-to-many relationship would be one client having many consultants working for them, and one consultant having many clients. In the Consulting database, this would be tracked using the Project table, and would look like this:


Many records in one table relate to many records in another table
Many records in one table relate to many records in another table
3) One-to-one:
This relationship is used to represent a one-to-one correspondence between two tables based on the primary key fields in both tables. For the purposes of this module, a new table has been created and added to the project database called ConsultantBankInfo. For the purposes of the course project, this table contains sensitive data that you may not want to include in the Consultant table itself. There will be one record in the ConsultantBankInfo table for each of the records in the Consultant table. Here is how the relationship looks in Access:

one-to-one correspondence between two tables based on the primary key fields in both tables
one-to-one correspondence between two tables based on the primary key fields in both tables
The symbols on the end of the join lines are as follows: 1 means one record, the infinity symbol means many.
In the next lesson, you will learn how to create one-to-one and one-to-many relationships.