Linking Tables in a Database

Creating Relationships

Microsoft Access allows you to link tables together so that records are maintained. For example, a primary table containing information about Customers such as Name, Address and Telephone number may be linked to Products Ordered. This will establish a link between customer details and the products they have ordered.

1. Create a table called ‘Customer Details’ with the following field names:

2. Customer Ref, First Name, Surname, Address, Telephone No.

3. Fill in details for each customer, for example:

• Customer Ref: 01265

• First Name: James

• Surname: Smith

• Address: 35 Main Street

• Telephone No. : 017384673

4. Create another table called Orders with the following field names:

Order Ref, Customer Ref, Order Date & Order Cost

5. Fill in the details for each order making sure some customers order more than one product and the Customer Ref field matches the entries in the Customer Details table:

• Order Ref: 46536

• Customer Ref: 01265

• Order Date: 05/6/2020

• Order Cost: €24.99

6. Save the database and leave it open

One-to-Many Relationships

A one-to-many relationship is when a primary table containing one field of data is linked to a table with all the details for that field. For example, a table containing Contact Details can be held in one table and can be related to a Company table where all the details for that company are held only once. This is a useful way of storing information about customers, products and companies.

1. On the Database Tools tab in the Relationships group, click on Relationships

2. In the Show Table dialog box, select the Customer Details table and click on Add

3. Do the same for the Orders table

4. Click and drag the Customer Ref field in the Customer Details table to the Customer Ref field in the Orderstable

5. In the Edit Relationships dialog box, click on the Create button

6. A One-To-Many relationship type is created

7. Return to the Customer Details table

8. Click on the Expand symbol to reveal the order details related to James Smith

9. This is called a Subdatasheet

10. Save the database

Enforcing Referential Integrity

This prevents Primary Key data in the main table from being changed or deleted or any data being changed or deleted in the Primary Table. In this example, Enforcing Referential Integrity will prevent details in the Customer Details table from being deleted or changed and data in the Customer Ref field for both tables from being changed or deleted. This is a useful feature that prevents accidental deletion or alteration of information within a database.

1. Right-click on the relationship between both tables

2. Choose Edit Relationship

3. Click on the Enforce Referential Integrity checkbox

4. Click OK

5. Save the database and close it.

To learn more about Microsoft Access, check out the Advanced ICDL Databases book below: