Last Updated 22 Feb 2022 Difficulty level : Moderate
Section Links: (this page)
Summary & Downloads
1. Introduction Return To Top
Many new users of Access are unsure about the differences between query joins and table relationships.
When a query is created, tables (and queries) can be joined in different ways (inner/left/right joins) whether or not a relationship has been defined at table level.
See this article: Types of Query Joins for more information.
Furthermore, different query joins can be used with any table relationships already created.
In truth, there are many similarities between query joins and table relationships.
If relationships have been applied, these will automatically appear for those tables when used in the query designer window.
However, that isn’t important enough to justify their use
As a result, there is some disagreement amongst developers about the use of table relationships
Some developers apply them rigorously to any tables with linked data. Others hardly ever use them.
However, relationships have another very important purpose – enforcing referential integrity.
Before explaining that concept, I will explore some of the ways that relationships can be created at table level
2. Using the Relationships window Return To Top
Click Relationships on the Database Tools ribbon to open the Relationships window.
Depending on your settings, you may find this already contains several system tables even in a new blank database.
Many developers remove these system tables from the relationships window by hiding these tables.
Right click anywhere on each table and select Hide Table
Doing this removes the tables from the window but it does NOT delete the relationship.
To create new relationships, add two or more linked tables into the window using one of the same methods available in the query designer:
a) Right click and select Add Table
b) Click the Design tab in the ribbon and select Add Table
c) Drag the tables into the window
Then join the tables using suitable fields in each table. By default, an inner join is created. For example:
The relationship can be edited or deleted by right clicking on the join line
If you click Delete, the relationship is removed
Click Edit Relationship to alter the relationship created
Click Join Type and a window familiar from the query designer will appear
The possible join types are INNER, LEFT OUTER and RIGHT OUTER.
The default is Option 1 (INNER)
For example, if Option 2 (LEFT OUTER) is chosen, the relationship diagram changes to:
As previously mentioned, the most important reason for using table relationships is to apply referential integrity (RI).
This is used to prevent orphan data remaining in a ‘child’ table after corresponding data is deleted in the ‘parent’ table.
First consider 2 tables joined but without applying referential integrity
In this example, the tables list several albums and album tracks. This shows the tracks for the album with ID=4
If that album is deleted in tblAlbums, the corresponding tracks are NOT deleted in tblAlbumTracks.
Those records are now orphaned
To add referential integrity (RI), click Enforce Referential Integrity on the Edit Relationships window then click OK
Depending on the fields you have joined, the join line will be marked:
a) 1-1 (one to one) where both fields are primary keys
b) 1-oo (1 to many) where one field is not a primary key (so multiple records are possible)
It is always better to apply RI before adding data to the tables
You will not be able to enforce RI if one table has records that are missing in the other table
If you try and delete album ID=4 now, Access prevents you doing so as there are related records in tblAlbumTracks
Similarly, it will not allow you to add a record in tblAlbumTracks for a non-existent album 5
To fix this issue, we need to check the cascade update/cascade delete options.
Doing so, ensures RI is retained when fields are updated and/or records are deleted
When you try to delete album #4 now, Access warns you of the consequences:
Clicking Yes deletes the corresponding records in both tables. This is Cascade Delete
Similarly, referential integrity automatically handles issues where the linked field value is updated. For example if the album with ID=4 is renumbered as ID=14 in tblAlbums, the corresponding field is updated in tblAlbumTracks ensuring that related records remain related. This is Cascade Update
It is NOT possible to enforce referential integrity between 2 fields unless one or both are indexed with no duplicates allowed.
Typically this will be the primary key field. Otherwise you have an indeterminate join.
Continue adding relationships as appropriate to other tables in your database:
The relationships can also be saved as a report from the Design ribbon when the relationships window is displayed.
However, the report layout isn’t very good and is difficult to modify
For large databases containing many tables, the relationships window can become very crowded
You can move/shrink items to help improve the layout to some extent.
However, by this stage, you may wish to hide some of the tables without deleting the relationships between them
For linked tables, the relationships need to be created in the linked backend database
You can display a backend table relationship in the frontend relationships window.
However, doing this will NOT over-ride any relationship already created in the backend
3. Summary & Downloads Return To Top
So far, this article has discussed the use of the relationships window to create table relationships as well as enforcing referential integrity.
In the second part of this article, I will explain two other methods of creating relationships between tables: subdatasheets and VBA code
Click to download:
a) This article as a PDF file: Relationships Advice
b) The sample database used in this article MSysRelationships Approx 1.5 MB (zipped)
Please use the contact form below to provide any feedback on this article including details of any errors or omissions.
Colin Riddington Mendip Data Systems Last Updated 22 Feb 2022