Last Updated 22 Feb 2022                                                                                       Difficulty level :   Moderate

Section Links: (this page)
          Introduction
          Relationships Window
          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



NOTE:
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)

NOTE:
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



NOTE:
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

NOTE:
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