Starting Relationships - The Microsoft Access Way

View your tables.

Once you’ve set up the required tables, click the relationship button and you will be presented with a screen similar to this one.

This shows all of the tables you have created in your database and all of the fields in each table. If you have set a primary key on any field it will appear in bold (Customer No in both tables are set as the primary key in this example).

Creating the first relationship.

In this instance, we would want to create a relationship between the two fields called Customer No. In order to do this, simply drag the field Customer No from the parent table to the child table.

In this instance the parent table is Customer ID because all information in the other table pertains to that one.

Once you’ve done this the ‘Edit Relationships’ dialog will pop up. You will notice near the bottom of the screen there are two options that are grayed out. In order to use either of these you must tick the box next to ‘Enforce Referential Integrity’.

Cascade Update Related Fields means when you change a primary key value in the Parent Table the value will be changed in all related records.

Cascade Delete Related Records means when you delete a record in the Primary Table, all associated records will also be deleted.

Join Type

There are 3 different types of join in an Access database and the one you need to select will depend entirely on your requirements. If the relationship is set up automatically for you then the correct relationship should be selected, and unless you have numerous different relationships set up it is unlikely that you will need to change this from selection 1, certainly not until you have more experience of working with the relational side of Microsoft Access.

As you can see creating relationships within Microsoft Access really is quite a simple procedure, knowing when to use them is the more difficult part. This is especially true if you’ve been using a spreadsheet that has simply grown too large and is also true if you want to use sub tables, sub forms or sub queries.

Without the use of queries you are seriously limiting your database capabilities. Setting up queries that involve anything more than one table will involve the creation of relationships. In most databases that utilize more than two tables you will undoubtedly have many relationships set up of many different types and if you do use sub queries or lookups then you will need even more relationships. It is important that you get to grips with basic relationships and how to create them. The best way to learn is to set up a small sample database of your own and give it a go. Initially try just using two or maybe three tables and then add more tables as you progress. Eventually you will know virtually everything there is to know about the relational database.