Creating Database Relationships in Access

Up to this point in Access, we have been working with independent tables, which could have been easily created in another application such as Microsoft Excel. Early in the workshop, we explained some advantages of using a relational database over a flat file database. Next we will focus on one of those advantages, the ability to create a relationship between our faculty table and our new departments table.

The most common type of relationship in Access is the one-to-many relationship. In this example, one department can have many faculty members, but each faculty member is assigned to only one department. The primary key is DeptCode, which is the common field that will relate or join these two tables.

The foreign key in the related table links to the primary key in the primary table. Records with a foreign key can contain duplicate values (unlike the primary key), or this field value can be left blank. When tables are related, there is less chance for error because fields do not have to be manually updated in multiple places; therefore, redundancy and inconsistencies are minimized or eliminated, and data within the database is much easier to locate and manage. For example, in this database, Access will keep track of information pertaining to each department on the DeptCode field; therefore, any information specific to any department has to be entered only once into tblDepartments.

We will learn more about the other types of relationships, one-to-one and many-to-many, in the course Access: Structuring and Relating Data.

Adding Tables to the Relationships Workspace

In order to set up the one-to-many relationship between our two tables in Access, we will need to make use of the Database Tools command tab.

Step1. To open the Database Tools tab, on the Ribbon,

Click the Database Tools command tab

Step2. To open the Relationships workspace,

Click

The Add Tables pane opens:

We will select the two tables in our database, so that we can define their relationship.

Step3. To select tblDepartments, if necessary,

Click tblDepartments

Step4. To select the second table, on the keyboard, press:

Shift key + Click tblFaculty

Step5. To add the tables, at the bottom of the Add Tables pane,

Click 

Two windows are opened in the workspace, one for each table, with a list of all of the fields for each one. We would like to see all of the fields at once, so we will resize the windows now.

Step6. To position the cursor,

hover over the bottom border of tblFaculty

Your cursor will change to a dual-sided arrow:

7. To increase the length of the tblFaculty window,

Press & Drag the bottom border of the window down until all of the fields are visible

Step8. Repeat steps 5 & 6 with tblDepartments, if necessary.

Notice that the Photo field in tblFaculty has some detailed information. This information is relevant for the photo itself, but we don't need to see it now.

Step9. To close the expanded Photo field information,

Click

Now we are ready to define the relationship between the two tables in our database.

Defining a Relationship Between Two Tables

Remember that there is one record for the Mathematics department in tblDepartments, but there are many faculty assigned to the DeptCode MATH in tblFaculty. Access will automatically recognize the one-to-many relationship because DeptCode is the primary key in tblDepartments. Access reminds us of the primary key for each table with this symbol:

The fact that DeptCode is the primary key in tblDepartments guarantees that the values in that field are unique. The same value for DeptCode, however, can appear multiple times in tblFaculty, for each faculty member in that department. Thus, this will be a one-to-many relationship by definition.

Let's create the relationship between our two tables on the DeptCode field.

Step1. To create the relationship, in the tblDepartments window,

Press & Drag DeptCode over to DeptCode in tblFaculty

When we let go of the mouse button, the Edit Relationships dialog box opens:

The relationship has been built: the DeptCode field from tblDepartments is mapped to the DeptCode field from tblFaculty. At the bottom of the dialog box, we see Access recognizes the relationship type as one-to-many, based on the primary and foreign key structure of the two tables.

Enforcing Referential Integrity

In this dialog box, we can also enforce referential integrity. Referential integrity is very important to good database design. It operates strictly on key fields, and prevents us from creating so-called "orphaned" records. Every time a key field is changed, either in the primary table or the related table, Access will check to make sure that the relationships will be kept intact and that the records in the database are not invalidated.

For example, we would not want to assign a faculty member a department code that does not exist. This might happen due to data entry error, such as typing MATS instead of MATH for a Mathematics faculty member. If you enforce referential integrity, Access will not allow you to enter that mistyped value.

Step1. To enforce referential integrity for this relationship,

Click the "Enforce Referential Integrity" checkbox

Once we enforce referential integrity, we have two additional options: cascade update related fields, and cascade delete related records.

Understanding Cascade Update Related Fields and Cascade Delete Related Records

If Cascade Update Related Fields is checked, we can make changes to fields in a related record. For example, if a department were renamed and we decided to change its department code in tblDepartments, cascade update would automatically update the department code field for all faculty in tblFaculty from that department. Enabling this option then becomes a question of business rules; do we want someone doing data entry to have the ability to make that kind of change? If not, or if we adopt as a business rule that our department codes will never change, then we should not select Cascade Update Related Fields.

On the other hand, we almost never want to enable Cascade Delete Related Records. If it's enabled, when we delete a record from the main table, all of the related records get deleted as well. For example, if a department was getting renamed or moved, and we decided to delete the department instead of updating the record, then all of the faculty assigned to that department would be deleted as well.

For our database, we'll just enable Cascade Update Related Fields, in case a department code changes at some point in the future.

Step1. To enable the correct option,

Click the "Cascade Update Related Fields" checkbox

Step2. To finish creating the relationship,

Click

Now we see a join line connecting the two tables in our relationships workspace.

The symbols 1 and also appear between the tables, indicating that referential integrity is established.

Let's close the relationships workspace.

Step3. To close the window, on the Ribbon,

Click

Before we can close the window, Access wants to know if we want to save the changes we made to the design to the layout. Keep in mind that this is not asking if we want to save the relationship itself; that has been saved already. This is an opportunity to save this particular layout of the two windows, when we made them larger to see all of the fields. We would like to save the larger windows.

Step4. To save the layout of the relationships workspace,

Click

The relationships workspace closes.

Now that we have our relationship established, we're ready to start building additional database objects, such queries, forms, and reports. We'll start with queries.