Add a lookup field to a table

Use a lookup field to find ("look up") values in one table that you can use in another table. A lookup field can provide values for a dropdown list and make it easier to enter data in a field – whether the data is in a datasheet, on a form, or in an Access web app view.

For example, let's say you want a list of the 50 U.S. states so your database users can pick a state from the list instead of typing its name. You use the Lookup Wizard in Access to set up a lookup field for the state name.

Create a lookup field in an Access web app

Create a lookup field in a desktop database

Create a list of values to use in a lookup field

In an Access web app

In a desktop database

Combo box vs. autocomplete lookup fields

Create a lookup field in an Access web app

Before you continue, if you used a database template to create your app or added a table by using a template, Access may have already created some lookup relationships between tables. To check for lookup fields, open a table in Design view in Access and look in the Data Type column for the Lookup data type.

To use the Lookup Wizard for an Access web app:

1. In the Access desktop program, open the table in Design view.

2. In the first empty row in the list of fields, type a name for the new lookup field and choose Lookup in the Data Type column. This starts the Lookup Wizard.

   Important: In an Access web app, you need to add a new field and immediately set it to use the Lookup Data type. If you try to change an existing field to the Lookup Data type, you won't be able to save changes to the table's structure – even if the field has never contained any values. This limitation applies only to a web app.

3. Click I want the lookup field to get the values from another table or query.

4. Choose the table or query you want to use as the data source.

5. In the Which value do you want to display in your lookup? list, choose the field or query you want to use for your lookup data.

6. Choose how to sort the values in the Do you want to sort the items in your lookup? list.

7. Choose an option to handle what happens when a record from the source table is deleted.

   Note: The Prevent delete if there are corresponding records… option is usually the best choice, because it helps retain data integrity. Use the other options with caution.

8. Click OK, and save the table.

Learn more about Data types for Access desktop databases and Data types for Access apps.

Create a lookup field in a desktop database

1. In Design view, choose an existing field or add a new field, and choose the Lookup Wizard data type.

2. On the first screen of the wizard, choose I want the lookup field to get the values from another table or query and click Next.

3. Under View, choose the table or query you want to use as the source. Then click Next.

4. Click Next to confirm your choice.

5. Choose a sort order, and then click Next.

6. Check the Hide key column (recommended) box to hide the key field of the source table, if it's not the field you want shown in the list. For example, if the first column contains a number and the second column contains the values you want to see, you probably don't want to see the first column.

7. Click Next.

8. If you haven't specified a name for the field already, the wizard uses the name of the field from the other table or the name of the source query.

9. Click Finish, and save the table.

   Tip: If you convert an existing field to a lookup field, Access warns you that any existing values may be lost when you convert it. If you need to save these values, make a copy of the table you're about to change, including its structure and data.

Create a list of values to use in a lookup field

Instead of using an existing field or a query as the source, you can create a list of values.

In an Access web app

1. In the Access desktop program, open the table in Design view.

   Important: In a web app, you need to use a new field. You won't be able to save changes to the table's structure if you change a field's data type to Lookup, even if the field has never contained any values.

1. Add a new field, and choose Lookup as the data type. This starts the Lookup Wizard.

2. Click the I will type in the values that I want box.

3. Enter the values, one on each line, then click OK.

In a desktop database

1. In the Access desktop program, open the table in Design view. You can add a new field or convert an existing field to a lookup.

2. In the Data Type column for the field, click Lookup Wizard.

3. Click the I will type in the values that I want button, and click Next.

4. Enter a value in each cell of Col1 in the grid (going down the column), and click Next when you've entered all the values you want.

5. Check the Limit to List box to prevent users from entering a value that isn't in the list.

6. Check the Allow Multiple Values box to let users choose more than one value for the field.

Learn how to Store multiple values in a lookup field.

1. Click Finish.

Combo box vs. autocomplete lookup fields

In the web app List view shown here, the State box is a combo list control that shows state names in a dropdown list. As you start typing the first or the first few letters of a state, you jump to the first match in the list. Use a combo box when people need to see all the choices from the list right away.

Learn how to Add a combo box to a view.

In this view, the Shipping Zone box is connected to the Zone field in the Shipping Zones table. The Shipping Zone box is an autocomplete box control instead of a combo box control. An autocomplete box is empty until you start typing – choices start appearing as you type characters that match items in the list. Use an autocomplete box when people know already what they're looking for, and don't want to have to scroll through a long list.

Learn how to Add an autocomplete box to a view.