Create a lookup field

Creating a lookup field in your Microsoft Office Access tables can help improve the efficiency of the data entry process for your database. A lookup field can display a user friendly value that is bound to another value in the source data table or value list. For example, the lookup field can display a contact name that is bound to a respective contact ID number in another table, query or list.

This article, explains the types of lookup fields and then shows you how and when to create each type.

What do you want to do?

Choose a lookup field type

Create lookup fields

When to use multiple values in a lookup field

Remove a lookup property

Choose a lookup field type

A lookup field displays a list of values from which the user can choose. This can make data entry quicker and more accurate. When data entered in a field already exists in another table, you can avoid duplication and possible errors from reentering the data. Lookup fields can be either simple or complex. Simple lookup fields let you store a single value while complex lookup fields or multi-value lookup fields let you store multiple values that are displayed in a combo box or a list box control. For each entry in a multi-value field, a display value is "looked up" based on its corresponding bound value. When a lookup field is viewed or accessed in a datasheet, it is called a lookup column. The two types of lookup fields that you can create are a lookup list and a value list.

Use a lookup list

When a lookup field is based on a lookup list, the field gets its data from an existing table or query in the database. In this type of lookup, the tables are related and when the values in the datasource changes, the current data are available in the lookup field.

Use a lookup value list

When a lookup field is based on a lookup value list, the lookup field gets its data from a list of values that you type in when you create the field. This type of lookup field is ideal when you have a limited set of values that do not change often.

Create lookup fields

You can create a lookup field in either Design or Datasheet view. To change an existing field to a lookup field, use the Design view and change the datatype. To add a new lookup field, either use an existing table or query as the datasource for your lookup field or create your own value list. The following sections explain both ways of creating a lookup field.

Create a lookup field based on a table or query data source

1. Open the table in Datasheet view and click the column header where you want to create the lookup field.

2. Click the Lookup & Relationship datatype.

   

3. In the Lookup Wizard select I want the lookup field to get the values from another table or query is selected, and then click Next.

Note: If a lookup field is linked to a SharePoint list, any edits to the data must be made to the SharePoint list.

1. To select the source for the lookup field value, select from the Tables, Queries, or Both options.

If the application was designed for the Web, but the source table is in the client mode, it must have a numeric primary key or Access displays an error message. If this happens, you must first create a numeric primary key for the table from the Design view and then return to the Web mode to create the lookup field. For more information on designing a table in the Web mode see, Build a database to share on the Web.

1. Select a table or query from the list, and then click Next.

2. From the Available Fields list, select the field that you want to add, and then click the > button to add it to the Selected Fields list. Ensure that the selected field or fields appear in the Selected Field list, repeat to add additional fields until you have added all the required fields, and then click Next.

3. If you selected multiple fields, and you want to specify sort options for the lookup field, select the down arrow next to the first sorting field, and then click the name of the field by which you want to sort the values.

4. To change the sort order, click the Ascending button, and then click Next.

5. To adjust the width of the lookup field, use the mouse to drag the column to the width of your choice, and then click Next.

Note: You can adjust the column width of the lookup field but if you reduce the width so that it is no longer visible, it will not be displayed.

Tip:  Leave the Hide key column check box selected so that anyone using the lookup column sees only the values that you want them to see in the field and not the values in the primary key field.

1. Type a label name for the lookup field.

2. To create a multi-value lookup column, select the Allow Multiple Values check box.

Note: Allow Multiple Values must be selected to enable storing multiple values. See the section, When to allow multiple values in a lookup field.

1. Click Finish, click the File tab, and then click Save.

Tip: To verify or change properties for a lookup field, open the table in Design view and the properties are available on the Lookup tab, under Field Properties.

Top of Page

Create a lookup field based on a value list

1. In the Navigation Pane, right-click the table that you want to work with, and then click Design View.

2. Click the cell in the Data Type column that corresponds to the column that you want to define as a lookup field, click the down arrow and select a datatype, and then click Lookup Wizard.

Note:  If Access displays a message that it is unable to start the Lookup wizard, you should change the field's datatype.

1. In the Lookup Wizard, select I will type in the values that I want, and click Next.

2. On the next page of the wizard, type the number of columns that you want in your lookup list.

Note: You can adjust the column width of the lookup field but if you reduce the width so that it is no longer visible, it will not be displayed.

1. Type the values that you want displayed in the lookup list, and then click Next.

You can edit a value list directly in the Row Source text box from the Lookup field properties.

1. If you specified more than one column in step 4, you must choose which column you will use to uniquely identify each value. In the Available Fields box, double-click the column that you want to use to uniquely identify each value, and then click Next.

Note: This step does not appear if you did not specify more than one column in step 4.

1. On the next page of the wizard, type a label name for the lookup field.

2. If you are creating a multi-value lookup column, select the Allow Multiple Values check box.

   Note: Allow Multiple Values must be selected to enable storing multiple values. See the section, When to allow multiple values in a lookup field.

3. Click Finish and click Yes to save your changes.

Top of Page

When to use multiple values in a lookup field

Consider allowing multiple values to display in the lookup field if any of the following conditions apply to the way in which your database is used:

• You have a current list of values that allows the user several data entry choices.

• If your database frequently accesses a SharePoint site.

• Your database is linked to a SharePoint site.

While allowing the use of multiple values in a field is useful, it can also reduce the functionality of the database if you move it to a server.

Caution: Once you create a multiple value lookup field whose data source is an existing table, you will not be able to change either the datatype or the field size of the lookup field without first deleting the relationships.

Set or change lookup field properties

When you use the Lookup Wizard, the lookup field properties are automatically set. However, you can modify these properties to change the behavior of a lookup field.

• In the Navigation Pane, right-click the table that you want to work with, and then click Design View.

• Click the field whose lookup properties you want to change.

• Click the Lookup tab.

• Click the drop-down arrow next to the property that you want to change, and select the option that you want.

The following table lists the lookup field properties that you can set or change.

Property	Action
Display Control	Depending on the control type, the type can be set to one of the following: Check Box Yes/No fields only. Text Box     Shows content that has been entered by a user. List Box     Shows a list of values in an open window. Combo Box     Shows selected values when closed and shows the available list of values when open. Note: Setting the Display Controlproperty to either Text Box or Check Box disables lookups.
Row Source Type	Choose whether to populate the lookup field with values from another table or query, or from a list of values that you specify. You can also choose to populate the list with the names of the fields in a table or query.
Row Source	Specify the table, query, or list of values that provides the values for the lookup field. When the Row Source Type property is set to Table/Query or Field List, this property should be set to the name of a table, query, or SQL statement that represents the query. When the Row Source Type property is set to Value List, this property should contain a list of values that are separated by semicolons.
Bound Column	Specify the column in the Row Source property that supplies the value stored by the lookup field. This value can range from 1 to the number of columns in the Row Source property.
Column Count	Specify the number of columns in the row source that can be displayed in the lookup column. This property can be an integer value from 1 to 255.
Column Heads	Specify whether to display column headings. The value can be No (default) or Yes.
Column Widths	Enter the column width for each column. For example, if you don't want to display a column, such as an ID number, you can specify 0 for the width.
List Rows	Specify the number of rows that appear when you display the lookup column.
List Width	Specify the width of the control that appears when you display the lookup column.
Limit To List	Choose whether a user can enter a value that isn't found in the list.
Allow Multiple Values	Specify whether the lookup field uses a multivalued field and allows multiple values to be selected. This setting can be No (default) or Yes. If you select Yes, the user will be able to select multiple values from the Row Source property for each record. For more information on using this option see; When to allow multiple values in a lookup field.
Allow Value List Edits	Specify if the items in a value list lookup field can be edited. When set to Yes, right-clicking the lookup field displays the Edit List Items menu option. If the lookup field has more than one column, this property is ignored.
List Items Edit Form	Specify an existing form that you can use to edit the list items in a lookup field.
Show Only Row Source Values	Show only values that match the current row source when Allow Multiples Values is set to Yes.

Top of Page

Remove a lookup property

1. In the Navigation Pane, right-click the table that you want to work with, and then click Design View.

2. Select the field whose lookup property you want to remove.

3. Click the Lookup tab.

4. In the Display Control drop-down list, click Text Box, and then save the change.

Top of Page