Table Design   «Prev  Next»

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:

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.

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.
Entering data into a lookup field using the lookup feature is more intuitive

Creating Lookup Field
In the next lesson, you will learn how to use the correct data type for optimal performance.