Creating a Lookup Field

A lookup list displays values from a table or query.

Create a lookup list by selecting Lookup Wizard as the field's Data Type.

Select a source for the lookup field's values.

Select the fields that contain the values.

There are two ways that a lookup field can get its list of values:

• From a list of values or options that you enter yourself. For example, you could add the values "FedEx," "UPS," and "AirBorne" to a Shipping field.

• From a list of values in a table or query. For example, instead of entering a CustomerID number, you could select the CustomerID from a list of names.

You can see an example of a lookup field in figure. Instead of you having to type a hard-to-remember CustomerID number, the lookup field displays more meaningful information, such as the customer's name, yet still stores the CustomerID number in the field. Lookup fields will make more sense once you have actually worked with them, so let's jump right into this lesson's exercise.

1. Close the tblCustomers table without saving your changes, then open the tblCustomerTours table in Design view.

   Let's use a lookup field to make the CustomerID field easier to view and add data to. Here's how to create a lookup field:

2. Click the Data Type box next to the CustomerID field, click the list arrow, and select Lookup Wizard, as shown in figure.

   The appears, as shown in figure, and asks if you want your lookup field to get its values from another table or query or if you want to type a list of options yourself. Since you want your lookup field to get its values from the tblCustomers table, you will select the first option.

3. Click Next.

   The next step in the Lookup Wizard is to select the table or query that contains the values for your lookup field. You want to look up customer names, so you would select the tblCustomers table.

4. Select the tblCustomers table and click Next.

   Now you have to select the fields that contain the values you want to display in your lookup field. The Lookup Wizard displays the field names in tblCustomers table that you can add to your lookup field. To add a field to your lookup field, double-click the field or select the field and click the button.

   This step can be a little confusing at first. You need to add the field that contains the value you want to enterthe CustomerID fieldbut you also want to add several fields that will display more meaningful information in the value list, such as the LastName and FirstName fields.

5. Double-click the CustomerID, LastName, and FirstName fields to add them to the lookup field, as shown in figure. Click Next when you're finished.

   The next step in the Lookup Wizard dialog box is selecting a sort order for your list. You can sort records by up to four fields, in either ascending or descending order. We don't want to assign a sort order right now, so let's move on to the next step.

6. Click Next.

   This next step allows you to adjust the width of the columns in your lookup list. To adjust the width of a column, drag its right edge to the width you want, or double-click the right edge of the column heading to get the best fit.

   You can also indicate whether or not to include the primary key in the column by checking or unchecking the "Hide key column" check box. Any primary key fields will be hidden by default to make the lookup field less confusing. As you can see, your primary key field, CustomerID, is hidden.

   If the table or query you are working with does not have a primary key, the "Hide key column" check box will not appear. The Lookup Wizard will instead include an additional step where you will be prompted to select the column that will act as the bound column.

7. Click Next. Complete the Lookup Wizard by clicking Finish.

   You've finished creating the lookup field! Let's test it out....

8. Click Yes to save your changes and then click the View button to display the table in Datasheet view.

   The CustomerID field still contains CustomerID numbers, but now it searches for and displays the customer's LastName fieldmuch easier to understand.

9. Click the CustomerID field for any record. Click the list arrow that appears in the field.

   Out pops a list of all the customers in the tblCustomers tableneat, huh? All you have to do is click a customer's name to add their CustomerID number to the CustomerID field.

10. Press Esc to close the list without selecting any options.

To create a lookup field:

1. display the table in design view.

2. click the field's data type box, click the list arrow, and select lookup wizard.

3. click the i want the lookup column to look up the values in a table or query option and click next.

4. select the table or query you want to use for the lookup list and click next.

5. select the fields you want to add to the lookup field and click next.

6. select a sort order for your list (optional) and click next.

7. adjust the width of the columns that will appear in the lookup list and indicate whether or not to include the primary key in the column. click next.

8. if prompted, select a column that will act as the bound column and click next.

9. enter a label for the lookup column and click finish.