Using Lookup Fields in Microsoft Access

Lookup fields in Microsoft Access allow users to enter data more intuitively by selecting meaningful text values while the underlying table stores numeric identifiers. This technique is commonly used when a table needs to reference related information stored in a separate table. For example, a Consultants table may capture each consultant’s last name and first name, while a separate ConsultingTypes lookup table stores the list of consultant-role categories such as Full Time, Part Time, or Contractor.
The lookup table below lists each consulting type with a unique key. This key, ConsultingTypeID, is the value stored in the main Consultants table. When a lookup field is configured, Access displays the descriptive text instead of the numeric key during data entry, making the interface more user-friendly.

Without lookup fields, users must memorize or reference the numeric values from the lookup table each time they enter data into the main table. Although the database remains correct, this makes data entry prone to mistakes and considerably slower. In a modern workflow—especially in Microsoft Access for Office 365—lookup fields streamline this process by connecting the numeric key to its descriptive equivalent automatically. The following figure illustrates how data entry appears when no lookup field is used.

Microsoft Access in Office 365 retains the core functionality of lookup fields from earlier versions but includes interface improvements for ease of use. While the Lookup Wizard behaves similarly across versions, Office 365 provides cleaner dialogs, improved visibility options, and better integration with relationship management. A few modernizing notes:

• Lookup Wizard location: Still selected from the Data Type dropdown but with updated visual styling.
• Column width auto-adjustment: Office 365 can auto-size columns in lookup lists to improve readability.
• Enhanced Relationship View: Lookup fields now integrate more transparently with the Relationships diagram, making it easier to verify foreign key dependencies.
• Improved error prevention: Access now provides clearer prompts when a lookup field is misconfigured or when the source table cannot enforce referential integrity.

Below is a modern, fully updated set of steps for creating a Combo Box lookup field in Access:

1. Open the Consultants table in Design View. Click the next available row under the last field name and type ConsultingTypeID.
2. Select the Data Type cell for this new field. Open the dropdown and choose Lookup Wizard….
3. When the wizard begins, select:
   “I want the lookup field to get the values from another table or query.” Then click Next.
4. Select the source table: Choose ConsultingTypes and continue.
5. Choose the fields to display: Add both ConsultingTypeID and Description. Click Next.
6. Sort order (optional): Sort by Description to make the dropdown intuitive.
7. Column widths: Hide the numeric key by reducing the width of ConsultingTypeID to 0". Keep Description visible.
8. Choose a label: Enter Consultant Type as the label for the lookup field.
9. Finish the wizard. Access now creates a Combo Box lookup that stores the numeric key internally while displaying descriptive text.

These steps reflect the updated Access interface found in Microsoft 365, while maintaining compatibility with older versions.