Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

A bit of Relationships advice - Part 1 (of 3)

Return to Home Page

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.

 

 

Colin Riddington      Mendip Data Systems        05/11/2018

Return to Top
* Required

To provide feedback on this article, please enter your name, e-mail address and company (optional) below, add your comment and press the submit button. Thanks

Return to Main Article

Click this link to download this article as a PDF file:    Improve Access File Security

 

 

 

 

I would be grateful for any feedback on this article including details of any errors or omissions

Difficulty level :   Moderate

 

Code Sample here

1.   Using the Relationships window

Click any image to view a larger version

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

MSysRel1

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.

MSysRel2 MSysRel3

Many developers remove these system tables from the window by hiding these tables.

Right click anywhere on each table and select Hide Table

NOTE:

This removes the tables from the window but 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:

•   Right click and select Add Table

•   Click the Design tab in the ribbon and select Add Table

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

MSysRel5 MSysRel4

The relationship can be edited or deleted by right clicking on the join line

If you click Delete, the relationship is removed

MSysRel7 MSysRel8 MSysRel6

Click Edit Relationship to alter the relationship created

Click Join Type and a window familiar from the query designer will appear

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

MSysRel9 MSysRel10 MSysRel11

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

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

MSysRel13 MSysRel14 MSysRel15

To add 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:

•   1-1 (one to one) where both fields are primary keys

•   1-oo (1 to many) where one field is not a primary key (so multiple records are possible)

 

NOTE:

It is best 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

 

MSysRel12

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

MSysRel17 MSysRel16

When you try to delete album #4 now, Access warns you of the consequences:

Clicking Yes deletes the corresponding records in both tables:

MSysRel18 MSysRel19 MSysRel20 MSysRel21

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

MSysRel22

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 override any relationship already created in the backend

So far, this article has discussed the use of the relationships window to create table relationships as well as enforcing referential integrity.

In the next part of this article, I will explain two other methods of creating relationships between tables: subdatasheets and VBA code

* Required
1 2 Return To Top Page 1 of 3 Return to Access Articles 3

Click to download this article as a PDF file:                                Relationships Advice

 

Click to download the sample database used in this article         MSysRelationships                 Approx 1.5 MB (zipped)

 

 

 

I would be grateful for any feedback on this article including details of any errors or omissions

To provide feedback on this article, please enter your name, e-mail address and company (optional) below, add your comment and press the submit button. Thanks

Click any image to view a larger version

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

MSysRel18A