Create a Lookup Field | CustomGuide
Access Basic

Create a Lookup Field

How to Create a Lookup Field in Access

Create a Lookup Field

A lookup field lets you add information to a field from a list of values. There are two ways that a lookup field can get its list of values:

  • From a Lookup List: A list of values in a table or query. For example, instead of entering a CustomerID number, you could select it from a list of customers.
  • From a Value List: 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.

Create a Lookup Field

  1. In Design View, click the Data Type box for the field you want to create a lookup field for.
  2. Click the Data Type 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. Double-click the fields you want to add to the lookup field and click Next.

    You can also add fields by selecting the field in the Available Fields list and clicking the arrow buttons to add or remove them from the Selected Fields list.

  6. (Optional) Select a sort order for your list and click Next.
  7. Adjust the column width and click Next.
  8. Select a field that identifies the row and click Next.
  9. Enter a label for the lookup column and click Finish. Click Yes to save the table.
    Create a Lookup Field

You can select to Allow Multiple Values. If you select this box, Access allows you to select multiple values from your list and store them in a single field (a multi-value field) For example, if a single product is made in two different countries, you can display both countries.

Use a Lookup Field

  1. Click any record in the column where you created the lookup list.
  2. Click the list arrow and select a value from the list.

    You can also select an option from a lookup field by typing the first few values of the entry and then selecting from the results.

    Create a Lookup Field

Access adds the value you selected to the field.