| Lesson 5 | Using lookup fields |
| Objective | Set up fields that display Combo Box and List Box controls for data input. |
Using Lookup Fields in Access
Using a relational database like Access can sometimes be confusing when you need to enter data into more than one table and when you want to know which of those values correspond to an entry in a lookup table.
An example of a lookup table is a table of consultant types, as shown below. Here are the entries for the consultant types available in the lookup table called ConsultingTypes:
The field called ConsultingTypeID will be the one mapped to the entry in the main table used, Consultants.
You can specify that you want to use a lookup table for a field entry by picking the Lookup Wizard for the Data Type property of the field. The Data Type property of a field tells Access what kind of information is going to be typed into the field: strings, numbers, and so on. There will be more on data types later in this module.
Access lets you set up descriptive values that display onscreen when you enter data for the two different tables. The graphic below shows how two tables would have to be opened to see which legitimate values could be entered into a field that refers to another table.
| ConsultingTypeID | Description |
| 1 | Full time |
| 2 | Part time |
| 3 | Temporary |
| 4 | Contractor |
The field called ConsultingTypeID will be the one mapped to the entry in the main table used, Consultants.
You can specify that you want to use a lookup table for a field entry by picking the Lookup Wizard for the Data Type property of the field. The Data Type property of a field tells Access what kind of information is going to be typed into the field: strings, numbers, and so on. There will be more on data types later in this module.
Access lets you set up descriptive values that display onscreen when you enter data for the two different tables. The graphic below shows how two tables would have to be opened to see which legitimate values could be entered into a field that refers to another table.
- Text in the Image
- Microsoft Access: Appears as the window title, indicating that the software displayed is Microsoft Access.
- Consultants: Table: The title of the upper table, showing consultant data.
- ConsultingTypes: Table: The title of the lower table, displaying different types of consulting roles.
- Ready: Status bar at the bottom, showing the application is idle.
- Table Fields of Consultants Table
- LastName: Contains the last names of consultants (e.g., Barker, Sams, Emerson).
- FirstName: Contains the first names of consultants (e.g., Christopher, Joseph, Paul).
- Consulting Type: Contains numeric values (1, 2, 3, 4) that likely correspond to different consulting types.
ConsultingTypes Table
- ConsultingTypeID: Contains IDs (1, 2, 3, 4) representing types of consulting roles.
- Description: Contains the names of consulting roles:
- 1 = Full Time
- 2 = Part Time
- 3 = Temporary
- 4 = Contractor
This setup appears to represent a relationship where each consultant has a consulting type linked by the "ConsultingTypeID" in the "ConsultingTypes" table, which serves as a lookup for the "Consulting Type" field in the "Consultants" table.
1) Entering data into a lookup field without using the lookup feature
Once you have used the Lookup Wizard to create a Combo Box control, entering data into a lookup field via a drop-down menu is a snap, as shown in the example below.
In modern versions of MS Access, a few of these steps for creating a lookup field have been streamlined, and there are some differences. Here are a few key areas that might be considered outdated or adjusted in newer versions:
The overall process, however, remains very close to what you’ve outlined, with mainly interface and navigation refinements rather than fundamental changes in creating lookup fields. If you’re using a version other than Access 2000, it’s worth checking for minor updates in the interface.
- Design View Navigation: Modern versions may present different menus, making navigation to the Lookup Wizard slightly different. However, the Lookup Wizard itself remains similar.
- Dropdown Navigation: Instead of scrolling to find "Lookup Wizard" at the bottom of the Data Type dropdown, some newer versions might list it more accessibly.
- Wizard Interface Adjustments: The Lookup Wizard interface has minor visual and layout changes, but the basic steps remain unchanged. Labels or field prompts may differ slightly.
- Table Selection and Field Display: Fields are still chosen similarly, but newer versions might offer additional options for customizing how the lookup displays.
The overall process, however, remains very close to what you’ve outlined, with mainly interface and navigation refinements rather than fundamental changes in creating lookup fields. If you’re using a version other than Access 2000, it’s worth checking for minor updates in the interface.
Creating a lookup field in MS Access for Office 365:
Here’s a revised version of the steps for creating a lookup field in MS Access for Office 365:
Steps for Creating a Lookup Field in MS Access (Office 365)
Steps for Creating a Lookup Field in MS Access (Office 365)
- Open the Consultants Table: Open Consulting307.mdb and switch to Design View for the Consultants table. To add a new field, click in the blank cell below the SSN field, type ConsultingTypeID in the Field Name column, and press Tab.
- Set Data Type: Next, select the Data Type cell next to ConsultingTypeID. Click the arrow to open the drop-down list.
- Choose Lookup Wizard: Scroll to the bottom of the Data Type list, then select Lookup Wizard.
- Choose Lookup Source: In the first Lookup Wizard screen, you’ll be prompted to select a source for the lookup values. Choose the default option, Look up the values in another table or query, and click Next.
- Select Record Source: Choose the ConsultingTypes table from the list of tables to use as the record source, then click Next.
- Choose Fields to Display: You’ll now select the fields to include in the lookup list. To add all fields, click the >> button. Here, the available field is ConsultingTypeID and its Description.
- Confirm Selected Fields: Once all desired fields are selected, click Next.
- Review Display Settings: On this page, preview how the lookup list will display the fields. By default, ConsultingTypeID will be hidden. Click Next.
- Set Lookup Column Label: To set a caption for the lookup column, replace ConsultingTypeID in the field labeled "What label would you like for your lookup column?" with Consultant Type.
- Complete the Lookup Wizard: With all settings finalized, click Finish to create the lookup column with the desired properties.