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:
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.
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:
- 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.
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)
- 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.
This updated guide reflects MS Access in Office 365, though the core process is similar to that in older versions.