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, press: Tab key
Step3. To select Lookup Wizard,
Click Lookup Wizard...
- 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.
Step4. To continue the wizard,
Step5. To choose tblRoles, in the list,
Click Table: tblRoles, Click
Step6. To select the Role field,
Double-Click Role, Click
Step7. To sort on the Role field, on the first drop down list,
Click, Click Role, Click
Step8. To accept the default to hide the key,
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.
Step10. To save the table and create relationships,
Step11. Save and close tblSectionsInstructors.
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
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
3. To begin deleting the relationship between tblFaculty and tblSectionsInstructors,
Click the join line connecting tblFaculty and tblSectionsInstructors, press: Delete key
Step4. To confirm the deletion,
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 this videoabout 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
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.
|Display Control||Determines the type of control implemented. The Wizard chose a Combo Box. Other options are Text Box and List Box. The list box is a list that stays open permanently as opposed to the combo box which only displays the list when it is clicked. A text box displays a static value.|
|Row Source Type||Determines where the data comes from, such as a table or query.|
|Row Source||Query that generates the fields in the list. Created by the Lookup Wizard.|
|Bound Column||Indicates which column in the list contains the foreign key.|
|Column Count||Specifies the number of columns in the list. In this case, there are three columns, even though the first column is hidden.|
|Column Heads||Determines whether or not the names of the fields (or captions) are displayed at the top of the lookup column.|
|Column Widths||Determines the width of each column. If the first column width setting is 0", the primary key field will not be displayed. This setting is determined in the Wizard when choosing whether to display or hide the primary key field.|
|List Rows||Tells Access how many rows to display at once. The default is 16.|
|List Width||Specifies the overall width of the displayed list. This value should be at least the width of all the columns plus.15" to allow for a vertical scroll bar.|
|Limit to List||Prevents the user from entering a value that isn't in the list. The lookup field will still accept null values unless the Required property for the field is set to Yes.|
|Allow Multiple Values||Determines whether the user can select multiple values from a list or combo box.|
|Allow Value List Edits||Sets whether the Edit List Itemscommand is available when the user right clicks a combo or list box. This allows the end user to add or edit list items.|
|List Items Edit Form||Sets the name of the form that is displayed when the user clicks Edit List Items.|
|Show Only Row Source Values||Sets whether the combo or list box can display values that aren't specified by the source row.|
Step2. To toggle the Column Heads property to Yes,
Double-Click the Column Heads field
Step3. To view the drop-down list of the Property Update Options,
Step4. To close the drop-down list, press:
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
Step3. To change some of the Role values,
Clickany empty Role field, Click,
Click any role
Step4. Save and close the table when you are finished.