Adding a Lookup Field to a Table Using the Lookup Wizard


Lookup lists are useful for fields that reference a specific list of items. Using a lookup field limits data entry to items in the lookup list. Data entry operators don't have to remember what the items are — the data choices are presented as a drop-down list. The lookup list can reference another table or query in your database, or it can list items that you enter through the Lookup Wizard.



To use the Lookup Wizard:

  1. Open the table in Design view.
  2. Click the field that you want to modify to use a Lookup list.
  3. Click the Data Type drop-down arrow and click Lookup Wizard...
  4. In step one of the Lookup Wizard, decide where your lookup list data will come from - from another table or query, or will you enter the values yourself? Make your selection and click Next to go to the next step.
  5. If the lookup values will come from a table or query:
    1. Click the name of the table or query you want to use. Click Next.
    2. Add the field or fields that contain the data you want displayed in your lookup list. Click Next.
  6. If you want to enter the values yourself:
    1. Enter the number of columns in your lookup list.
    2. Press Tab to go to the first field in the blank column.
    3. Type the first entry. Press Enter or Tab to go to the next field.
    4. Continue entering data until you have completed your list. Click Next to go to the next step.
  7. Give your list a meaningful name. By default, the name will appear the same as the field that uses the list.
  8. Click the Finish button to complete the lookup list.


Usually, a lookup list will only have one column; however, multiple columns are useful when fields are related (such as part and sub-part numbers).

Notice that after using the Lookup Wizard, the data type for the field changes to number.

You can also add a lookup column to a table in Datasheet view. You can only use this method to add a new column to the datasheet. To modify an existing field to use a Lookup list, use the Design view method.

  1. In Datasheet view, right-click the field selector of column to the right of the place where you want the new Lookup column to appear. Select Lookup Column... to start the Lookup Wizard.
  2. Complete the wizard, following the same steps as if you had created the Lookup field in Design view.