| Lesson 3 | One-to-one and one-to-many relationships |
| Objective | Create one-to-one and one-to-many relationships. |
One-to-One and One-to-Many Relationships in Access
The reason I have combined creating one-to-one and one-to-many relationships is that Access will pretty well determine what type of relationship you will be using, based on which fields you use for the relationship and whether they are primary keys.
One-to-one relationship
Creating the one-to-one relationship found in Consulting407.mdb is done by using the ConsultingID field in both tables. Both tables use the ConsultingID field as a primary key. Remember that when a field is a primary key, each record has to have a unique value in that field. Here are the table structures for Consultants and ConsultantsBankInfo:
The way to create this relationship is to:
- Choose the Relationships… option from the Tools menu to open the Relations Layout window.
- Clear any current tables displayed in the Relationship window by clicking the Clear Layout toolbar button
Clear Layout toolbar button - Add the two tables that will be used, in this case Consultants and ConsultantsBankInfo, by clicking the Add Table toolbar button,
Clear Layout toolbar button
and adding the two tables using that dialog. - After the two tables have been added, use drag and drop by clicking and holding the mouse on the field called ConsultingID in the table Consultants, then dragging it over the ConsultingID field in ConsultantsBankInfo. The Edit Relationships dialog will open, as seen here:
Notice that the relationship type is one-to-one. Access specified this based on the fields used and the tables' primary keys.
One last thing to note is the way to get the 1 and infinity symbol to be displayed. Click the Referential Integrity check box.
Referential integrity will be discussed in greater detail in the next lesson.
One-to-many Relationships
Creating one-to-many relationships is identical to creating one-to-one relationships. The main difference is that the field used in the “many” side is not a primary key field. The field used on the many side will be what is called a foreign key. For instance, the ClientNo used in Projects is not the primary key, but a foreign key. In Clients the ClientNo is a primary key.
In the next lesson, you will learn how to create a many-to-many relationship.
One To One Relationship - Exercise
Click the Exercise link below to practice creating one-to-one relationships.
One To One Relationship - Exercise
One To One Relationship - Exercise