Customize how data is displayed by creating a lookup field

When you want a field in a datasheet, form, or report to display a certain value while the field stores a different value, you create a lookup field. A lookup field is useful when you want the field to display something more meaningful than a number, such as an ID (or other foreign key value). For example, instead of displaying a contact ID number, Microsoft Office Access can display a contact name. In this example, the contact ID number is called the bound value, or the value that is stored in the source table, query, or list. The contact ID number is automatically looked up in the source and replaced with the contact name. The contact name is referred to as the display value.

Note: The difference between a lookup field's display value and its bound value is that the display value is displayed in Datasheet view while the bound value is the stored value.

When a lookup field is viewed or accessed in a datasheet, it is called a lookup column.

Lookup fields can be either simple or complex. Simple lookup fields let you store only a single value. Complex lookup fields or multi-value lookup fields let you store multiple values that are displayed in a combo box or a list box control. For each entry in a multi-value field, a display value is "looked up" based on its corresponding bound value. Providing a choice of lookup values helps avoid data entry errors by limiting the values that can be entered.

What do you want to do?

Create a new lookup field

Change an existing field to a lookup field

Remove a field's lookup property

Set or change lookup field properties

Create a new lookup field

You can create a lookup field that will retrieve values from a table or a query, or from a list of values that you create.

Note: When you provide a list of values for a lookup field, the values in the field of the current record are automatically entered.

Select lookup values from a table or query

1. In the Navigation Pane, right-click the table that you want to work with, and then click Design View.

2. Click the cell in the Data Type column that corresponds to the column that you want to define as a lookup field, click the down arrow, and then click Lookup Wizard.

3. In the Lookup Wizard, ensure that the I want the lookup column to look up the values in a table or query option is selected, and then click Next.

4. To view and select the source for the lookup field value, on the next page, under View, select the Tables, Queries, or Both option, and then click Next.

5. Under Avaliable Fields, select the field that you want to add.

6. Click the select button .

7. Repeat this step if you want to add more fields, and then click Next.

8. To specify sort options for the lookup field, on the next page of the wizard, select the down arrow next to the first sorting field, and then click the name of the field by which you want to sort the values.

9. To change the sort order from ascending to descending, click the Ascending button, and then click Next.

10. To adjust the width of the lookup field, on the next page of the wizard, use the mouse to drag the column to the width that you want, and then click Next.

    Tip: Leave the Hide key column check box selected so that anyone using the lookup column sees only the values that you want them to see in the field and not the values in the primary key field.

11. On the next page of the wizard, type a label name for the lookup column.

12. To create a multi-value lookup column, select the Allow Multiple Values check box.

    Note: This check box must be selected to enable storing multiple values.

    

13. Click Finish.

Tip: The properties that apply to a specific lookup field are available on the Lookup tab, under Field Properties.

Select lookup values from a list

1. In the Navigation Pane, right-click the table that you want to work with, and then click Design View.

2. Click the cell in the Data Type column that corresponds to the column that you want to define as a lookup field, click the down arrow, and then click Lookup Wizard.

3. In the Lookup Wizard, select I will type in the values that I want, and then click Next.

4. On the next page of the wizard, type the number of columns that you want in your lookup list.

5. Type the values that you want in the lookup list, and then click Next.

   Note: You can adjust the width of the lookup field but if you reduce the width so that it is no longer visible, it will not appear in the lookup column.

6. If you specified more than one column in step 4, you must choose which column you will use to uniquely identify each value. In the Available Fields box, double-click the column that you want to use to uniquely identify each value, and then click Next.

   Note: This step does not appear if you did not specify more than one column in step 4.

7. On the next page of the wizard, type a label name for the lookup column.

8. If you are creating a multi-value lookup column, select the Allow Multiple Values check box.

   Note: This check box must be selected to enable storing multiple values.

9. Click Finish, and then clickYes to save your changes.

Top of Page

Change an existing field to a lookup field

1. In the Navigation Pane, right-click the table that you want to work with, and then click Design View.

2. Click the cell in the Data Type column that corresponds to the column that you want to define as a lookup field, click the down arrow, and then click Lookup Wizard.

3. Follow the instructions to complete the wizard.

Top of Page

Remove a field's lookup property

1. In the Navigation Pane, right-click the table that you want to work with, and then click Design View.

2. Select the field whose lookup property you want to remove.

3. Click the Lookup tab.

4. In the Display Control drop-down list, click Text Box.

Top of Page

Set or change lookup field properties

When you use the Lookup Wizard, the lookup field properties are automatically set. However, you can modify these properties to change the behavior of a lookup field.

1. In the Navigation Pane, right-click the table that you want to work with, and then click Design View.

2. Click the field whose lookup properties you want to change.

3. Click the Lookup tab.

4. Click the drop-down arrow next to the property that you want to change, and select the option that you want.

   The following table lists the properties that you can set or change.

Property	Action
Display Control	Depending on the control type, the type can be set to one of the following: Check Box     Yes/No fields only. Text Box     Shows content that has been entered by a user. List Box     Shows a list of values in an open window. Combo Box     Shows selected values when closed and shows the available list of values when open. Note: Setting the Display Control property to either Text Box or Check Box disables lookups.
Row Source Type	Choose whether to populate the lookup field with values from another table or query, or from a list of values that you specify. You can also choose to populate the list with the names of the fields in a table or query.
Row Source	Specify the table, query, or list of values that provides the values for the lookup field. When the Row Source Type property is set to Table/Query or Field List, this property should be set to the name of a table, query, or SQL statement that represents the query. When the Row Source Type property is set to Value List, this property should contain a list of values that are separated by semicolons.
Bound Column	Specify the column in the Row Source property that supplies the value stored by the lookup field. This value can range from 1 to the number of columns in the Row Source property.
Column Count	Specify the number of columns in the row source that can be displayed in the lookup column. This property can be an integer value from 1 to 255.
Column Heads	Specify whether to display column headings. The value can be No (default) or Yes.
Column Widths	Enter the column width for each column. For example, if you don't want to display a column, such as an ID number, you can specify 0 for the width.
List Rows	Specify the number of rows that appear when you display the lookup column.
List Width	Specify the width of the control that appears when you display the lookup column.
Limit To List	Choose whether a user can enter a value that isn't found in the list.
Allow Multiple Values	Specify whether the lookup field uses a multivalued field and allows multiple values to be selected. This setting can be No (default) or Yes. If you select Yes, the user will be able to select multiple values from the Row Source property for each record. For more information about multivalued fields, see the article Guide to multivalued fields.
Allow Value List Edits	Specify whether you can edit the items in a lookup field that is based on a value list. When this property is set to Yes, and you right-click a lookup field that is based on a single column value list, you will see the Edit List Items menu option. If the lookup field has more than one column, this property is ignored.
List Items Edit Form	Specify an existing form to use with which you can edit the list items in a lookup field that is based on a table or query.
Show Only Row Source Values	Show only values that match the current row source when Allow Multiples Values is set to Yes.

To programmatically look up a value in a table, see the reference article DLookup Function.

Top of Page