Creating the SectionsInstructors table

The data we need to create tblSectionsInstructors already exist in tblSections. To make the new junction table, we can copy tblSections, give it a new name, and adjust its fields accordingly. Once the table is created, we will modify the relationships to include the new table.

Another way of creating a copy of a table is to simply copy it from the Navigation pane, then paste. This will prompt us to give the new table a name. Let's create a copy of tblSections named "tblSectionsInstructors".

Step1. To select tblSections, in the Navigation pane,

Click tblSections

Step 2. To copy tblSections, on the keyboard, press:

Control key + C

Step 3. To paste tblSections and give it a new name, on the keyboard, press:

Control key + V, type: tblSectionsInstructors Enter key

Step 4: To open tblSectionsInstructors, in the Navigation pane,

Double-Click tblSectionsInstructors

We now have the table we will use to represent multiple instructors in our database.

Modifying the New Table

Before we go on, let's define the two fields in tblSectionsInstructors as a primary key and remove unnecessary fields. This will ensure that we only assign an instructor to a particular section once. We also need to set the data type for SectionKey to be number instead of autonumber. This way we can build a relationship with the autonumber key field in tblSectionsMultiInst.

This section has less guidance than the previous sections. If you would like a walk-through, watch the video Modifying the New Table.

Step1. In Design view, set the data type for SectionKey in tblSectionsInstructors to number.

NOTE: We don't want SectionKey to be an AutoNumber field anymore, but in order to match the data type of the AutoNumber SectionKey field in tblSectionsMultiInst, the data type needs to be a Long Integer Number. The number data type defaults to long integer in this case.

Step2. In Design view, delete all fields except SectionKey and InstructorID.

NOTE: When we copied tblSections, we also copied its indexes. Since we're deleting several fields, we will also be deleting those indexes. This is the desired outcome since the fields aren't required in the final table anyway.

Removing Indexes

In order to create a one-to-many relationship between tblSectionsInstructors and tblSectionsMultiInst, we must first remove the indexes we defined in tblSections. Since we copied the table to create tblSectionsMultiInst and tblSectionsInstructors, we also copied the indexes. That's fine for tblSectionsMultiInst since we want it to be as close to tblSections as possible, but it's not OK for tblSectionsInstructors since we want to be able to assign the same instructor to multiple sections.

The first step in removing indexes is to temporarily unassign the primary key field.

Step1. To remove the primary key from tblSectionsInstructors,

Click the SectionKey field, Click

Now that the key has been removed, we can go into the Indexes dialog box and remove all the remaining indexes.

Step2. To open the Indexes dialog box, on the Ribbon,

Click

We can simply select every row in the dialog box and delete the indexes.

Step3. To delete the table's indexes, select every row, press:

Delete key

Let's close the Indexes dialog box.

Step4. Close the Indexes dialog box.

Now we can re-assign the primary key as a combination of InstructorID and SectionKey since each faculty member can only have one role per section.

Step5. Create a composite key of SectionKey and InstructorID.

Step6. Save and close tblSectionsInstructors.

Modifying Relationships

Now that we have created our new table, it's time to change how our database relationships are configured to include the new table. We want to relate tblSections to tblSectionsInstructors via SectionKey and tblFaculty to tblSectionsInstructors via InstructorID/EmployeeID. Lastly, we want to remove the relationship between tblSections and tblFaculty. First, let's build the relationship between tblSections and tblSectionsInstructors and between tblFaculty and tblSectionsInstructors.

This section has less guidance than the previous sections. If you would like a walk-through, watch the video Modifying Relationships.

Step1. Add tblSectionsMultiInst and tblSectionsInstructors to the Database Relationships window.

Step2. Edit the database relationships based on the following table:

When you have finished, your Relationships window should look something like this:

NOTE: Notice how tblFaculty and tblCourses are still related to both tblSections and the new tables we added. We will fix that shortly.

Let's remove tblSections from the Relationships window to make things easier to visualize. Remember, this will not remove the relationships between tblSections and other tables, it just hides it from the layout in the Relationships window.

Step3. To hide tblSections,

Right-Click the title bar for tblSections, Click Hide Table

The table is now hidden.

Step4. If desired, Reorder the tables in the Relationships window to make the relationships easier to read.

Step5. Save and close the Relationships window.

Deleting a Field

Before the multiple instructor functionality is complete, we need to remove the InstructorID field from tblSectionsMultiInst as it is no longer needed and causing a normalization problem. Specifically, we are storing the InstructorID for a section in two separate tables: tblSectionsMultiInst and tblSectionsInstructors. Let's delete the InstructorID field from tblSectionsMultiInst now since we will rely on the InstructorID in tblSectionsInstructors from now on.

Step1. Open tblSectionsMultiInst in Design view.

Step2. To delete the InstructorID field,

Click, press: Delete key

Access prompts that we will be permanently deleting the selected fields and all the associated data:

Since this is a redundant field with data we have already captured elsewhere, we can safely say yes.

Step3. To continue deleting InstructorID,

Click

Access then warns us that we are deleting a field that is part of an index:

If you recall the Indexes: tblSections dialog box from earlier, there was an index for InstructorID:

Since we copied tblSections to create tblSectionsMultiInst, the indexes copied as well. We don't need the data in tblSectionsMultiInst anymore, so it is ok if we delete the index as well.

Step4. To continue deleting InstructorID,

Click

InstructorID has been removed from tblSectionsMultiInst. We can now save and close our table.

Step5. Save and close tblSectionsMultiInst.

We've added the multiple instructor functionality, but before we remove the old functionality (assigning a single instructor to a section), let's make sure we haven't broken anything in our database.