Identify different types of relationships used in Access.
Three Types of Relationships in Microsoft 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.
There are three types of relationships in Access:
One-to-many Relationship
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:
Here is the transcription of the two Microsoft Access tables shown in the diagram:
Clients Table
| Field Name |
| ---------- |
| ClientNo |
| Company |
| FirstName |
| LastName |
| Address |
This means one client can have many projects.
One-to-many: One record in a table relating to many records in another table
Many-to-many Relationship
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-to-many: Many records in one table relate to many records in another table
One Consultant can be assigned to many Projects (1:M)
One-to-one Relationship in Access
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:
This enforces that each consultant has at most one related bank info record, and vice versa.
One-to-one: 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. The following page describes the three relationship types used in data modeling described using entity relationship diagrams[1]. Three Relationship Types using ERD.
In the next lesson, you will learn how to create one-to-one and one-to-many relationships.
[1]entity relationship diagram: An entity relationship diagram (ERD) is a graphical representation that depicts relationships among people, objects, places, concepts or events within an information technology system.
