Store multiple values in a lookup field

You can create a lookup field that stores multiple values per record. This helps you keep track of multiple related facts about a subject. For example, suppose you have an issue tracking database and you want to keep track of the employees who are working on the issues. One employee might be working on several issues, and each issue might have more than one employee working on it. This kind of data structure is called a many-to-many relationship. Access makes it easy to keep track of this kind of related data by using a multivalued lookup field.

This article explains what a multivalued lookup field is, how to add one to a table, and how to change an existing lookup field.

Note:  This article doesn't apply to Access apps – the new kind of database you design with Access and publish online. See Create an Access app for more information.

In this article

Overview

Create a multivalued lookup field in Datasheet view

Create a multivalued lookup field in Design view

Use the Lookup Wizard to create a multivalued lookup field

Review or change lookup field properties in Design view

Change the design of a multivalued lookup field

Overview

A lookup field is a table field that has values that come either from a table or query or from a value list. When you click a lookup field on a Datasheet to enter data, Access displays a list of values to choose from. A lookup field can be multivalued, which means you can choose more than one item in the list. You can display the choices in a combo box or a list box.

Creating a multivalued lookup field

You can create a multivalued lookup field in Datasheet view or in Design view. In either view, you use the Lookup Wizard to create the multivalued field. The Lookup Wizard simplifies the process and automatically populates the appropriate field properties and creates the appropriate table relationships.

Modifying a multivalued lookup field

You can re-run the Lookup Wizard to modify an existing multivalued lookup field.

Bound values and display values

When you create a lookup field that gets data from a table or query, Access uses the key field from the source table to determine which value goes with which record. However, key fields usually have data that isn't intrinsically meaningful – usually an ID field with the AutoNumber data type. A lookup field replaces the display of the key field with something more meaningful, such as a name. The value that is stored is called the bound value. The valued that is displayed is called the display value.

For example, a set of employee ID values from a query named Contacts Extended might be stored in a field named AssignedTo. However, because AssignedTo is a lookup field, Access displays the set of looked-up values in the query result — in this, case the contact names. The employee ID values are the bound values. The contact names are the display values.

1. The display values.

2. The bound values.

Why bound values matter

By default, the display values are automatically shown when you open the table in Datasheet view. However, the bound values are what is stored in the lookup field, what you use in query criteria, and what Access uses by default in joins with other tables.

For example, if you have a query that uses the AssignedTo lookup field as depicted in the preceding image and you want to exclude records that represent issues assigned to Eva Valverde, the criterion that you use to exclude her must use the bound value (which is 9), not her name; i.e., WHERE AssignedTo.Value <> 9.

Top of Page

Create a multivalued lookup field in Datasheet view

You can add a multivalued lookup field to a table that is open in Datasheet view.

1. In the Navigation Pane, double-click the table in which you want to create the lookup field — this opens the table in Datasheet view.

2. To the right of the last column, click Click to Add, and then click Lookup & Relationship.

   The Lookup Wizard starts.

3. Follow the remaining steps of the wizard. For more information, see the section Use the Lookup Wizard to create multivalued lookup fields.

Top of Page

Create a multivalued lookup field in Design view

You can create a multivalued lookup field while working in Design view. When you work in Design view, you create a lookup field by using the same Lookup Wizard that you use in Datasheet view. However, in Design view, you also have full access to all of the field properties, so you can edit those properties directly.

1. In the Navigation Pane, right-click the table in which you want to add the multivalued lookup field, and then click Design View.

2. Locate the first available empty row in the table design grid.

3. In the first available empty row, click in the Field Name column, and then type a field name for the multivalued lookup field.

4. Click in the Data Type column for that row, click the arrow and then, in the drop-down list, select Lookup Wizard.

   The Lookup Wizard starts.

5. Follow the steps in the wizard. To learn how to complete the Lookup Wizard, see the section Use the Lookup Wizard to create multivalued lookup fields.

6. After the wizard finishes, you can edit the lookup field properties. To learn more about lookup field properties, see the section Review or change Lookup field properties in Design view.

When you click Finish, a lookup field is created whose field properties are set based on the choices you made in the Lookup Wizard. You can view the field properties in the bottom pane of Design view under Field Properties. To see the properties that apply specifically to the lookup field, click the Lookup tab.

Top of Page

Use the Lookup Wizard to create a multivalued lookup field

You can use the Lookup Wizard to create a field that is based on a table or query, or that is based on a list of values that you type. This section describes the steps of the Lookup Wizard.

Create a multivalued lookup based on a table or query

1. In the Lookup Wizard, click I want the lookup field to look up the values in a table or query, and then click Next.

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

3. Under Available Fields, click the fields that you want included in your lookup.

4. Click the "greater than" button (>) to move the fields you chose to the Selected Fields list. Click the double "greater than" button (») to move all of the fields to the Selected Fields list, and then click Next.

5. Optionally, select one to four fields on which to sort the lookup items, and then click Next.

6. Adjust the width of the columns in your lookup field, if necessary, and then click Next.

7. Under Do you want to store multiple values for this lookup?, select the Allow Multiple Values check box.

   Note: This check box must be selected to enable storing multiple values.

8. Click Finish.

   When you click Finish, a lookup field is created whose field properties are set based on the choices you made in the Lookup Wizard.

9. To save the table, on the File tab, click Save.

   Keyboard shortcut  CTRL+S

Create a multivalued lookup based on values that you enter

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

2. Enter the number of columns. Then, type each value. To move to the next column or row, press TAB.
   

   Note:  If you want to be able to edit the value list, use only one column.

3. When you are finished entering values, click Next.

4. If you specified more than one column at step 2, 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.
   

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

5. In the Lookup Wizard, type the label for your lookup field.

6. Under Do you want to store multiple values for this lookup?, select the Allow Multiple Values check box.
   

   Note: This check box must be selected to enable storing multiple values.

7. Click Finish.

8. To save the table, on the File tab, click Save.

   Keyboard shortcut  CTRL+S

After you create the multivalued field, it appears as a check box drop-down list in Datasheet view.

Top of Page

Review or change lookup field properties in Design view

You can review the lookup field properties in the bottom pane of Design view, under Field Properties. To see the properties specifically related to the lookup field, click the Lookup tab.

1. In the Navigation Pane, right-click the table in which you want to add the lookup field, and then click Design View on the shortcut menu.

2. Click the lookup field's name in the Field Name column.

3. Under Field Properties, click the Lookup tab.

   The lookup properties appear.

   

When you set the value of the first property (Display Control), the list of available properties changes to reflect your choice. You can set the Lookup field properties to change the behavior of a lookup field. Note that when you use the Lookup Wizard to create a lookup field, the Lookup field properties are set for you by the wizard.

Lookup Field Properties

Set this property	To
Display Control	Set the control type to Check Box, Text Box, List Box or Combo Box. Combo Box is the most common choice for a lookup field.
Row Source Type	Choose whether to fill the lookup field with values from another table or query, or from a list of values that you specify. You can also choose to fill 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 a table or query name or to a 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 separated by semicolons.
Bound Column	Specify the column in the row source that supplies the value stored by the lookup field. This value can range from 1 to the number of columns in the row source. Note: The column that supplies the value to store does not have to be the same column as the display column.
Column Count	Specify the number of columns in the row source that can be displayed in the lookup field. To select which columns to display, you provide a column width in the Column Widths property.
Column Heads	Specify whether to display column headings.
Column Widths	Enter the column width for each column. If you don't want to display a column, such as an ID column, specify 0 for the width.
List Rows	Specify the number of rows that appear when you display the lookup field.
List Width	Specify the width of the control that appears when you display the lookup field.
Limit To List	Choose whether you can enter a value that isn't in the list.
Allow Multiple Values	Indicates whether the lookup field allows multiple values to be selected. Note:  You cannot change the value this property from Yes to No.
Allow Value List Edits	Specify whether you can edit the items in a lookup field that is based on a value list. When this property is set to Yes and you right-click a Lookup field that is based on a single column value list, you will see the Edit List Items menu option. If the lookup field has more than one column, this property is ignored.
List Items Edit Form	Name an existing form to use to edit the list items in a lookup field that is based on a table or query.
Show Only Row Source Values	Show only values that match the current row source when Allow Multiples Values is set to Yes.

The bound value in a lookup field is determined by the Bound Column property. The display value in a lookup field is the column or columns that are represented in the Column Widths property as having a non-zero width.

Top of Page

Change the design of a multivalued lookup field

To change the design of a multivalued lookup field, you can do any of the following:

• Edit the value list of a list-based multivalued lookup field
  

  Note:  To enable edits, the value list must have only one column and the Allow Value List Edits property must be set to Yes.

• Edit the lookup field properties in Design view

• Modify a lookup field by running the Lookup Wizard with the lookup field selected.

Edit the value list of a multivalued lookup field that is based on a value list

You can add to the list of values, change the list of values, or set a default value.

1. In the Navigation pane, double-click the table that contains the multivalued field.

   The table opens in Datasheet view.

2. Right-click any cell in the multivalued lookup field.

3. Click Edit List Items.

4. Change the value list and then click OK.

Edit the lookup field properties in Design view

To change a multivalued lookup field, you can open the table in Design view and modify the Lookup field properties. For information about the lookup field properties, see the section Review or change lookup field properties in Design view.

Modify a multivalued lookup field in Datasheet view

You can modify a lookup field that in Datasheet view by re-running the Lookup Wizard

1. Open the table in Datasheet view and select the lookup field.

2. On the Fields tab, in the Properties group, click Modify Lookups.

3. Follow the steps in the wizard to make the changes that you want. For more information about the wizard, see the section Use the Lookup Wizard to create a multivalued lookup field.

Top of Page