Sign in with Microsoft
Sign in or create an account.
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

Important    Access Services 2010 and Access Services 2013 are being removed from the next release of SharePoint. We recommend you do not create new web apps, and migrate your existing apps to an alternative platform, such as Microsoft Power Apps.

You can share Access data with Dataverse, which is a cloud database upon which you can build Power Platform apps, Automate workflows, Virtual Agents, and more for the web, phone, or tablet. For more information, see Get started: Migrate Access data to Dataverse.

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. For example, let’s say you want a list of the 50 U.S. states so your users can pick a state from the list instead of typing its name. You can use the Lookup Wizard in Access to set up a lookup field for the state name.

In this article

Create a lookup field

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 apps.

Top of Page

Create a values list field

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

  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.

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

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

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

Top of Page

Comparing Combo box and autocomplete controls

In the following view:

Typing in an autocomplete box

  • The Shipping Zone field uses an autocomplete control. As you start typing the first or the first few letters of a zone, you jump to the first match in the list. Use an autocomplete control when people know already what they're looking for, and don't want to have to scroll through a long list. For more information, see Add an autocomplete control to a view.

  • The State field uses a combo box control. Click the down arrow to see the complete list of values. Use a combo box when people need to see all the choices from the list right away. For more information, see Add a combo box to a view.

Top of Page

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!