Creating a Lookup Field

The Lookup Wizard helps you create a field that displays either of two kinds of lists during data entry: a Lookup list that displays values looked up from an existing table or query, or a Value list that displays a fixed set of values you enter when you create the field. Because values are limited to a predefined list, using Lookup fields helps you avoid data entry errors in situations where only a limited number of possible values are allowed. The lists are not limited to a single column. You can include additional columns that could include descriptive information for the various choices in the list. However, only a single column, called the bound column, contains the data that is extracted from the list and placed into the Lookup field.

Create a Field Based on a Lookup List

Display the table in Design view, enter a new field, click the Data Type list arrow, and then click Lookup Wizard.

Click the I Want The Lookup Column To Look Up The Values In A Table Or Query option, and then click Next.

Select the table or query you want to use for the Lookup list, and then click Next.

Select the fields that you want to appear in the Lookup list, and then click Next.

Choose a sort order for the Lookup list, and then click Next.

Resize the column widths in the Lookup list, indicate whether or not to include the primary key column, and then click Next.

If the table or query lacks a primary key, you will be prompted for the column that will act as the bound column. Otherwise the primary key will be the bound column.

Enter a label for the Lookup column, and then click Finish.

Create a Field Based on a Value List

Display the table in Design view, enter a new field, click the Data Type list arrow, and then click Lookup Wizard.

Click the I Will Type In The Values That I Want option, and then click Next.

Specify the number of columns you want in the Value list.

Enter the values in the list. If necessary, resize the column widths. Click Next to continue.

Choose which column will act as the bound column, and then click Next.

Enter a label for the Lookup column.

Click Finish.