Microsoft Access Lookup Wizard

The Microsoft Access Lookup Wizard is a very useful feature. It appears as one of the field data types, and is used for fields which have a restricted list of possible values. This may be either if the field is a foreign key (in which case the appropriate value is usually drawn from the list of values in the matching primary key) or some non-key field such as Title of Courtesy, which may be restricted to Mr, Miss, Mrs etc. The lookup wizard may be applied to text and numeric fields, the latter including dates. If applied to a foreign key field, then it may create the appropriate table relationship.

i) Non-key field.

Suppose we have a table containing data about our employees, and wish to add a field storing their grade. This may be one of the following: Senior, Associate, Junior, Trainee. The Employees table is opened in Design view, and the new field inserted:

Microsoft Access Lookup Wizard Non-key Field

The data type of Grade is set to “Lookup Wizard”, automatically launching the Microsoft Access Lookup Wizard. In choosing this, we are telling Access that the possible values are to be drawn from a list. The first stage in the wizard consultation is to ask what the source of the list is. It may be entered manually by the user, or drawn from a separate table or query:

Microsoft Access Lookup Wizard

The option to type the values is chosen. In the next stage the list of possible values is entered:

Microsoft Access Lookup Wizard Possible Values

The last stage of the wizard includes options to limit the user to selecting from the list and whether multiple values can be selected:

Microsoft Access Lookup Wizard Limit List

“Limit To List” would prevent the user from entering a value that is not in the list. As such, it is one of Access’s data integrity checks. It is not, however, always appropriate. It may be that the “best of both” approach is needed; the convenience of selecting from a list of the most likely values combined with the flexibility of entering a value manually if the required value is not in the list (for example Dr. or Prof. for the title of courtesy). When “Finish” is selected, the user is returned to the table in Design view. It is then necessary to set the field size to 9, this being the length of the longest entry (“Associate”).

Microsoft Access Lookup Wizard Limit to List

On selecting the “Lookup” tab, the following settings are shown:

Microsoft Access Lookup Wizard Lookup Tab

If “Limit To List” is set to “Yes”, then “Allow Value List Edits” should be set to “No”. This setting allows the user to enter a value not included in the list, and then specify that it should be added to the list, effectively editing the table design; not good practice. Note that the list may easily be edited; the different values are separated by semi-colons. The column width may also be changed, for example to allow longer entries. If the list values are numbers, then they would not be enclosed in quotes.
In datasheet view, a drop-down menu is displayed on selecting the field:

Microsoft Access Lookup Wizard Datasheet View

If an invalid value is selected, then a warning message is displayed if “Limit To List” is set to “Yes”:

Microsoft Access Lookup Wizard Invalid Value

If a form is created that includes a Lookup Wizard field then a combo box control is automatically added to the form.

ii) Foreign key field; unique non-key field.

If the field is a foreign key, then the Microsoft Access Lookup Wizard is doubly helpful; it will make it easier for the user to enter the appropriate value and may also create the relationships between the tables. A foreign key’s values are generally restricted to the list of values in the matching primary key. As primary keys are usually short and simple (sometimes just numbers) it can be difficult for the user to select the appropriate value.

Suppose, using the Employees table above, we decide to add a further field listing which department the employee works for. We create the following Departments table:

Microsoft Access Lookup Wizard Departments Table

The Dept Code field is the primary key. The Dept Name field is unique (a “no duplicates” index has been applied to it).

As the Dept Name field is unique, it is an easy matter to set things up so that, when entering the foreign key value, the user selects from a list of department names; “behind the scenes” however, it is the Dept Code value that is stored.

Firstly a foreign key field, “Department” is added to the Employees table, and the data type is set to Lookup Wizard.

Microsoft Access Lookup Wizard Use Another Table

This time, the list of values is drawn from a table, so the first option is selected. In the next stage, the Departments table is chosen:

Microsoft Access Lookup Wizard Select Table

In the third stage, the appropriate fields are selected:

Microsoft Access Lookup Wizard Select Fields

Although any number of fields may be selected, it is generally best to choose just two; the primary key (whose value is being used) and one other field that is unique to each record. In this case, as the department name is unique to each department, it is a good choice. At a minimum, just the primary key could be chosen if it is sufficiently user-friendly.

The sort order may be specified next, if required:

Microsoft Access Lookup Wizard Select Sort Order

In the fifth stage, the following dialogue is displayed:

Microsoft Access Lookup Wizard Column Width

If more than one field has been selected, then Access will hide the (primary) key column; it is assuming that this is not very user-friendly.

In the final stage, the appropriate options for the relationship are selected:

Microsoft Access Lookup Wizard Relationship Options

“Enable Data Integrity” would create a relationship with referential integrity switched on, ie every employee must have a matching department.

In Datasheet view for the Employees table, a drop-down list of department names is displayed:

Microsoft Access Lookup Wizard Datasheet View

The user can select from the list of names and it appears that this is the field’s value; fundamentally, however, it is the three-letter code that forms the Department table’s primary key that is stored in the foreign key field.

iii) Foreign key; no unique non-key field

If the table does not have a unique field (apart from the primary key), then best practice is to create a query, based on the table, with two fields in it; one the primary key, the other a calculated field combining two or more fields that together are unique. In this way you can still use the Microsoft Access Lookup Wizard.

For example, suppose we have an Orders table linked to the aforementioned Employees table. None of the Employee fields, apart from the primary key, may be relied upon to be unique, but the First and Last Name fields combined are. A query is created, based on the Employees table, containing the Employee ID and a calculated field, Full Name, that concatenates the First Name and Last Name:

Microsoft Access Lookup Wizard Foreign Key no Unique Non-key Field

When run it shows the following:

Microsoft Access Lookup Wizard Foreign Key No Unique Non-key FieldExample

The foreign key field “Employee” is added to the Orders table and the Lookup Wizard selected. The above query is chosen as the source. The consultation is the same as before, except that the primary key field should be hidden manually:

Microsoft Access Lookup Wizard Hide Key Field

An extra stage is included where the primary key (here the EmployeeID field) should be selected as the field whose value is to be used:

Microsoft Access Lookup Wizard Select Value Field

In addition, the relationship between the Employees and Orders table would not be created if the lookup source is a query, so must be done manually.

When selecting a value for the foreign key field, a drop-down list of full names is displayed:

Microsoft Access Lookup Wizard Dropdown List of Names

As before, a form based on the table would have a combo box control with the same drop-down list created.

As we can see from the examples, the Microsoft Access Lookup Wizard is versatile and increases data integrity as well as speeding up data entry.

Click here for more details on Microsoft Access training courses.

Microsoft Access Lookup Wizard Links

Microsoft Access with Lookup Wizard
Microsoft Access Wikipedia

John Pascall
13 September 2016