Why create relationships between tables?

Access is a relational database. It allows multiple tables to be linked with each other via common fields. The links can be made from the relationship diagram (Database Tools, Relationships) and the linking fields must have the same data type.

 Quite often the Primary Key from one table is linked to the same field in another table (termed a foreign key).

 But why do we have multiple tables in the first place?

 The main reason for creating databases with several tables is to avoid duplication of data and allow changes to be made most efficiently.

 In this example suppose there are 300 employees and 10 departments. Without the department table the name, location and floor would have to be input 300 times. Using the 2 tables, only the DepartmentID needs to be entered.

 And in the future if a department location and floor changes there is no need to edit each employee record.

One to Many

When a primary key field is linked to a foreign field in a second table, a one to many relationship is created. For example

One employee has many orders.

One order has many order detail records.

One customer has many orders.

One to One

When a primary key field in one table is linked to a primary key field in another then a One to One relationship is created. For example

 Employee records linked to payroll records.

The reason here for creating a one to one relationship is to keep the payroll data separate from the main employee data.  It would make sense to have both cascade update and delete on for these tables.

Many to Many

A further type of relationship that occurs in a relational database is called many to many.

For example,

Many customers can buy from one sales employee and many sales employee can sell to one customer.

 So there is a many to many relationship between tblCustomers and tblEmployee.

 However, there are no common fields between these tables so they can’t be linked directly. What is needed is a third table called a conjunction table (or mapping table).

 The table tblOrder is the conjunction table that links between two tables in a many to many relationship.

 Queries or reports can then be set up to show customers for each employee:

or employees for each customer:

  To make this data easier to read, a query property called unique values is set to yes. (no duplicate records).

Join Type

As well as there are different types of relationship, there are also different types of join.

The default join type is called an inner join where queries produce only data that is matched by both tables. For example, when querying customers and orders, a query shows customers that have orders and not customers that haven’t.

Once a relationship is created between 2 tables,  further options become available for controlling how data is entered or removed from the tables.

Enforce Referential Integrity – Ticking this option (see above diagram) prevents data being entered into the secondary table (tblOrder)  if the EmployeeID doesn’t exist in the primary table (tblEmployee).

In addition, a record from the Primary table cannot be deleted if there are records in the secondary table with that EmployeeID.

Cascade Update - Ticking this option changes an EmployeeID in the secondary table if the EmployeeID field is changed in the secondary table.

Cascade Delete – Ticking this option deletes all related records if a record in the primary table is deleted.

It would be preferable to include both cascade update and cascade delete when linking tblOrders to tblOrderDetails.

 Supposing an order is cancelled then deleting the order record will in turn remove related records for that order held in the secondary table (tblOrderDetails).

Lookup fields are very useful for data entry. They help to avoid invalid data being entered into tables and forms. They can be set to lookup into a different table and can even display several columns of data. The field that stores the data is called the bound column.

 For example, from the Orderingdb database open tblEmployee in design view.

We can change the Gender field to a Lookup by right clicking on its Data Type and choosing Lookup Wizard.

Access offers 2 choices; to look up from a different table or to create a lookup by typing in the values. For the Gender field choose the second option.

You can then type M and F as the values to choose from and click Next.

Select Limit to List if you want to prevent other values being typed (such as Male or Female)

Click Finish and you will see the lookup field in Datasheet view.