Creating a Lookup Table

Access gives us many different tools to make data entry easier. One of those tools is called a lookup field. A lookup field displays a list of values from which to choose, which enables users to enter data more quickly and efficiently. The most common type of lookup field is called a lookup list, which gets its values from an existing table or query. The lookup is created in the foreign key field (child table), and Access will reference the primary key value from the primary or parent table by using a drop-down list. The advantage of this type of lookup is that since the tables are related, when the underlying data change in the primary table, the data will also update in the lookup field in the related table.

Database designers often apply a number as a unique identifier to records in a database. However, most people recognize a person or an object by a name more readily than by an identification number. Therefore, we can set up a table, query, or form to look up and/or display descriptive text rather than numbers to help us when we view or input data.

Using the Lookup Wizard

Access provides a Lookup Wizard to make the process of creating lookup fields or lists more simple. It creates a relationship between two tables for us, with certain settings. We will use the Lookup Wizard to create a lookup field for instructor role in tblSectionsInstructors. We would like to be able to assign an instructor's role in a section by picking it from a list of available roles.

Importing a Table

We don't have a table in our database that represents roles yet, so we will need to import one.

Step1. Import the spreadsheet InstructorRoles.xlsx as "tblRoles" letting Access determine the primary key and use the first row as field names.

Adding a Lookup Field to a Table

Now that the data for instructor roles is in our database, let's create a lookup field in tblSectionsInstructors that will allow us to assign an instructor a role based on the values in tblRoles.

Step1. Open tblSectionsInstructors in Design view.

Step2. To create a new field, in the empty row below InstructorID, type:

Role Tab key

Lookup fields are created by using the Lookup Wizard in the Data Type column of Design view.

Step3. To select Lookup Wizard,

Click, Click Lookup Wizard...

The Lookup Wizard dialog box appears:

NOTE: As seen in the previous screen shot, some of the dialog boxes in newer versions of Access have a display bug that causes the far right side of the dialog box to be cut off. Your dialog box may look slightly different than what is shown here as a result.

We are presented with two options:

• I want the lookup field to get the values from another table or query. This option lets Access look for values in another table or query. This option is useful if the values already exist in our database or if the list of values will change often.
• I will type in the values that I want. This option stores the values in the lookup field as a part of the field itself and not a separate table. This option is useful if the list of values is anticipated to never change.

We want the values to come from tblRoles, so we will keep the first selection.

Step4. To continue the wizard,

Click

We can now select a query or a table to be used as the source data:

Step5. To choose tblRoles, in the list,

Click Table: tblRoles, Click

The next screen allows us to choose the fields we want to be visible in our lookup field:

Since ID does not have useful information, we will only include the Role field.

Step6. To select the Role field,

Double-Click Role, Click

We see the sorting options for the Lookup Wizard:

We only have the Role field, so let's sort on it so that the roles will be listed in the lookup in alphabetical order.

Step7. To sort on the Role field, on the first drop down list,

Click, Click Role, Click

The next screen allows us to hide the key column:

The key column (which means the primary key field of the lookup table) is how the relationship will be built. You may think it should be shown, but hiding it will make the lookup list more user-friendly. When you select a role from the list by name, Access will use the key associated with that role to build the relationship.

It's a good idea to hide the key column since it's an automatically generated number and not as recognizable as the named roles.

Step8. To accept the default to hide the key,

Click

We've now reached the last screen of the wizard:

When we create a lookup field, we're creating a relationship between two tables. This final screen allows us to enable data integrity and decide whether or not to cascade delete. We would like to enable data integrity, another name for referential integrity we discussed earlier, and restrict deleting.

Step9. To enable data integrity and exit the wizard,

Click the Enable Data Integrity checkbox, Click

NOTE: This option will not turn on Cascade Update Related Fields. If you desire for that to be set, you will have to go to the Relationships window and modify the relationship between tblRoles and tblSections.

We are prompted to save the table before relationships can be made:

Let's save.

Step10. To save the table and create relationships,

Click

Let's save and close tblSectionsInstructors.

Step11. Save and close tblSectionsInstructors.

Let's create one more lookup field and test them both at the same time.

Understanding Multivalued Lookup Fields

The multivalued lookup field can display multiple values that are referenced and looked up from the primary table. For example, in this database, we could create a multivalue lookup in tblSections for faculty which would allow us to assign multiple faculty to a section by clicking their respective checkboxes. While this may sound appealing from an interface point of view, the truth is that multivalue fields have significant drawbacks and should thus rarely, if ever, be used.

Technically, an Access multivalued field simulates a many-to-many relationship. Access stores the values independently and manages them in hidden system tables. In the rare cases when we don't need a more advanced database design, a multivalued field may be a simpler option to allow us to store and select multiple choices. A multivalued field works best when the list of choices in that field is relatively small and if we are sure that the database will not be moved to a Microsoft SQL server or otherwise modified at a later date.

This type of field can become very cumbersome and difficult to manage. Once a multivalue field is created, it cannot be changed. Functionality can also be reduced using a multivalued lookup field if the database is moved to a server.

Chances are, if you are conceptualizing a many-to-many relationship, it is best to stick with a junction table rather than a multivalued lookup field. Junction tables offer more flexibility and are much easier to change if your database design needs to be updated.

Looking Up Instructors By Name

Before we test out our lookups, since we're in Design view already, let's turn our InstructorID into a lookup field, too. Before we can do that, we have to first remove the relationship between tblFaculty and tblSectionsInstructors. This is because the Lookup Wizard insists on creating a relationship for us. If one already exists, the wizard will not let us create the lookup.

Breaking a Relationship

We will delete the relationship between tblFaculty and tblSectionsInstructors so we can create the lookup field.

We can't edit a relationship for a table that is open, so let's save and close tblSectionsInstructors.

Step1. To view our relationships, on the Ribbon,

Click the Database Tools tab, Click

Since we renamed tblSectionsMultiInst to tblSections, it was removed from our database relationships layout. Let's add it back.

Step2. To add tblSections and tblRoles to the relationships layout, in the Navigation pane,

Press & Drag tblSections to the relationships window, Press & Drag tblRoles to the relationships window

You should see:

3. To begin deleting the relationship between tblFaculty and tblSectionsInstructors,

Click the join line connecting tblFaculty and tblSectionsInstructors, press: Delete key

Access will warn us that we're about to delete a relationship. Since we're going to recreate the relationship immediately, this isn't a concern for us. Let's confirm the deletion.

Step4. To confirm the deletion,

Click

The relationship between tblSectionsInstructors and tblFaculty is deleted.

Step5. Close and save the Relationships window.

Creating a Lookup Field

Now let's create a lookup field to make it easier to assign faculty to sections.

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

Step1. In tblSectionsInstructors, turn InstructorID into a lookup field pointing to tblFaculty.

Step2. Be sure to include LastName, FirstName, and DeptCode, and list faculty members in alphabetical order by LastName, then FirstName.

Step3. When you have finished, save the table.

Modifying Lookup properties

The Lookup Wizard has set the properties for the InstructorID field based on our selections in the dialog boxes. We can view them on the Lookup tab of the Field properties pane.

Step1. To view Lookup properties for the InstructorID field, with the InstructorID field selected, in the Field properties pane,

Click the Lookup tab

You see properties similar to these:

NOTE: Since you might make your own design changes in the previous Creating a LookUp Field exercise, your field properties might be slightly different, and that's ok.

There are several properties, which specify the appearance and behavior of the lookup field:

We will modify the Lookup properties so that the column headings show at the top of the Lookup drop-down list. Displaying column heads will add a row at the top of the drop-down list that will display the field captions.

Step2. To toggle the Column Heads property to Yes,

Double-Click the Column Heads field

The Column Heads field is changed to Yes. Now it will be easier to determine which column is the last name and which is the first name.

A Property Update Options tag appears, allowing us to update all Lookup properties everywhere that InstructorID from tblSectionsInstructors is used. This tag allows any other objects, such as forms and queries, that have been created to inherit the new Lookup properties.

Step3. To view the drop-down list of the Property Update Options,

Click

You see:

Since we don't have any current objects that need to be updated, we will ignore this for now.

Step4. To close the drop-down list, press:

Esc key

The drop-down list is closed.

Step5. Save the table.

Using Lookup Fields

Let's switch to datasheet view and use the lookup fields to change some of the values for some of the records.

Step1. Switch to datasheet view.

Step2. To change some of the values for InstructorID,

Clickany InstructorID value, Click, Click any other instructor name

The instructor is added to the course. Let's assign them a role.

Step3. To change some of the Role values,

Clickany empty Role field, Click, Click any role

The instructor has been assigned a role. Let's save and close the table.

Step4. Save and close the table when you are finished.

We've created two lookup fields. Now we will see how they facilitate data entry.