Access 2016: Create a Lookup Table

Access 2016 provides a Lookup Wizard that makes it easy to create lookup tables.

A lookup table is a table that contains data that is referenced by another table. The other table will have a lookup field that can "lookup" the data in the lookup table.

In Access, the lookup field displays the data as a drop down list (or combo box) so that the user can select the desired value from the list. The values of the lookup field come directly from the lookup table.

We will now turn our Genres table into a lookup table. The Albums table will then have a lookup field that gets its values from the Genre table.

  1. Screenshot of the Lookup Wizard option in Design View

    Launch the Lookup Wizard

    Open the Albums table in Design View.

    In the GenreId field, click in the Data Type cell and select Lookup Wizard from the combo box.

    The Lookup Wizard will appear.

  2. Screenshot of the Lookup Wizard

    Choose how the Lookup Field will get its Values

    Leave the default option (I want the lookup field to get the values from another table or query.) selected and click Next >.

  3. Screenshot of the Lookup Wizard

    Choose the Lookup Table

    Here, you get to choose which table will provide the values for your lookup field.

    Select Table: Genres and click Next >.

  4. Screenshot of the Lookup Wizard

    Choose the Field/s to display in the Lookup Field

    You can choose which field will be displayed in the lookup field of the table referencing the lookup table.

    Double-click Genre so that it moves to the right pane, then click Next >.

  5. Screenshot of the Lookup Wizard

    Choose the Sort Order for the Lookup Field

    You can choose how the data will be sorted in your lookup field.

    For this example, we will sort by the Genre field in ascending order.

    Select Genre from the first drop down, then click Next >.

  6. Screenshot of the Lookup Wizard

    Select the Column Width of the Lookup Field

    Access gives you the opportunity to specify the width of the column/s in your lookup field. If our field contained longer values, we might widen it, but the default width looks pretty good.

    You can also choose to display or hide the lookup table's primary key column. Leave it hidden.

    Click Next >.

  7. Screenshot of the Lookup Wizard

    Choose a Lable for the Lookup Field

    You can provide a label for the lookup field. In our case, let's shorten it to Genre.

    Also, check Enable Data Integrity.

    Click Finish to generate the lookup table.

  8. Screenshot of the Lookup Wizard

    Save the Table

    If you get prompted to save the table, click Yes and save it.

  9. Screenshot of the Lookup Wizard

    Check your Lookup Field

    Your lookup table (and corresponding lookup field) has now been created.

    Check your lookup field by switching to Datasheet View and clicking in the Genre field.