Our expert trainers can help you get the best results.
If you would like any further information please contact one of our training advisors.
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.
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:
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:
The option to type the values is chosen. In the next stage the list of possible values is entered:
The last stage of the wizard includes options to limit the user to selecting from the list and whether multiple values can be selected:
“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”).
On selecting the “Lookup” tab, the following settings are shown:
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:
If an invalid value is selected, then a warning message is displayed if “Limit To List” is set to “Yes”:
If a form is created that includes a Lookup Wizard field then a combo box control is automatically added to the form.
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:
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.
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:
In the third stage, the appropriate fields are selected:
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:
In the fifth stage, the following dialogue is displayed:
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:
“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:
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.
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:
When run it shows the following:
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:
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:
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:
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.
Payments processed by
