Relationships in Access

Tables in a database can be related so that information is not duplicated. You could have a Customer Details table that contains the contact information for each customer. This could be related to an Orders table providing details of every order that was made. Rather than having to repeat the customer details for every order, you can create a One-To-Many relationship so that one record in the Customer Details table is related to many records in the Orders table. This is because some customers are likely to order more than one product from a company

Tables are related using Key Fields

The first table will likely have the Primary Key

The matching field in the other table will have the Foreign Key

Creating Relationships

Access allows you to link tables together so that records are maintained. For example, a primary table containing information about Customers 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 the details for each customer

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

5. Order Ref, Customer Ref, Order Date & Order Cost

6. Fill in the details for each order making sure some customers order more than one product

7. 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 of the details for that company are held only once.

1. Open the ‘Customer Details’ database

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

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

4. Do the same for the Orders table

5. Click and drag the Customer Ref field in the Customer Details table to the Customer Ref field in the Orders table

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

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

8. Return to the Customer Details table

9. Click on the Expand symbol to reveal the order details related to Stephen Dunne

10. This is called a Subdatasheet

11. Save the database

For more information about Microsoft Access visit www.digidiscover.com or click the link below:

Get Office 365 on Amazon: https://amzn.to/2YmgLKq