access

BUAD 2020 Access2- Database Relationships

Relational databases consist of more than one table. These tables must share common fields in order for Access to work properly and be able to store related data in multiple tables and also retrieve related data from multiple table. To do this, we must create the necessary relationships between tables.

In the example below, we can see that both the Employee and Salary tables contain the Employee ID field, so we will need to create a relationship between these tables on the Employee ID field.

Employee Table

Salary Table

Creating the Relationships

1. Click the Database Tools tab.

2. Click the Relationships button.

When the Show Table dialog box appears, you must select the tables for which you

will be establishing relationships. In this case we will need two of the three tables.

3. Select the Employee table.

4. Click Add.

5. Select the Salary table.

1

2

6. Click Add.

7. Click the Close button to close the Show Table dialog box.

The Employee and Salary tables now appear in the Relationships window.

Next we need to define the kinds of relationships between the tables. We have already determined that a relationship needs to be established between these two tables using the Emp ID field. To create this relationship:

8. Highlight the Emp ID field in the Employee table.

9. Next holding your left mouse key down, drag your mouse to the Emp ID field in the Salary table.

10. When the Edit Relationships dialog box appears, make sure that you have elected

the correct fields from the correct tables.

3

5

7 4 & 6

Note the kind of relationship being created. This is a one-to-one relationship, since a

relationship is being created between the primary keys in both tables.

11. Click Create.

There is now a line joining the two tables in the Relationships window.

12. Close the Relationships window.

13. Click Yes.

11

13