Add or change a lookup column that lets you store multiple values

Sometimes, the values you need are stored in another table. For instance, suppose you have an issue tracking database and you want to keep track of the employees and contractors to whom you have assigned an issue. Information about these people is tracked in the Contacts table. You can create a multivalued lookup column that displays names in a combo box or a list box control. When you select the people to whom you want to assign an issue, their contact ID values are stored in the issue record.

This article explains what a multivalued lookup column is and how to add one to a table. The article also explains how to convert an existing column to a multivalued lookup column.

If you want to know how to programmatically lookup a value in a table, see the reference article DLookup Function. For more information about multivalued fields, see Guide to multivalued fields.

In most database management systems, including earlier versions of Access, you can store only a single value in a field. Starting with Microsoft Office Access 2007, you can create a field that holds multiple values, such as a list of people to whom you have assigned an issue. Multivalued fields are appropriate for certain situations, such as when you use Office Access 2007 to work with information stored in a Windows SharePoint Services 3.0 list, and that list contains a field that uses one of the field types available in Windows SharePoint Services 3.0.

What is a multivalued lookup column?

A multivalued lookup column is a field in a table whose values are retrieved from another table or from a value list. Using a multivalued lookup column, you can display a list of choices in a combo box or list box. The choices can be supplied by a table or query, or they can be values that you enter. Because the lookup column is multivalued, you can choose more than one item in the list.

You can create a multivalued lookup column manually by setting a field's Lookup field properties, or automatically by completing the Lookup Wizard. Whenever possible, you should use the Lookup Wizard to create a multivalued lookup column. The Lookup Wizard simplifies the process and automatically populates the appropriate field properties and creates the appropriate table relationships.

A multivalued field combo box

You can create a multivalued lookup column in Datasheet view or in Design view.

Top of Page

Create a multivalued lookup column in Datasheet view

By default, when you open a table, it appears in Datasheet view. You can add a multivalued lookup column by clicking Lookup Column in the Fields & Columns group on the Datasheet tab. This starts the Lookup Wizard, which leads you through the process of creating the lookup column.

Click the Microsoft Office Button , and then click Open.
In Open dialog box, select and open the database.
In the Navigation Pane, double-click the table in which you want to create the lookup column — this opens the table in Datasheet view.
On the Datasheet tab, in the Fields & Columns group, click Lookup Column.

The Lookup Wizard starts.
On the first page of the Lookup Wizard, you must decide whether you want to base the lookup column on the values in a table or a query or on a list of values that you type.

The most common type of multivalued lookup column is one that displays values looked up from a related table or query.
To learn how to complete the Lookup Wizard, see the section Using the Lookup Wizard.

Top of Page

Create a multivalued lookup column by using the Field List pane

If you are using a database that contains more than one table, you can start the process of creating a multivalued Lookup field by dragging a field from the Field List pane. The Field List pane displays the fields available in other tables in your database. When you create a multivalued lookup column this way, the Lookup Wizard is automatically started and the field that you drag becomes one of the fields in your lookup column.

Click the Microsoft Office Button , and then click Open.
In Open dialog box, select and open the database.
In the Navigation Pane, double-click the table in which you want to create the lookup column — this opens the table in Datasheet view.
On the Datasheet tab, in the Fields & Columns group, click Add Existing Fields.

The Field List pane appears.
Click the plus sign (+) next to a table to display the list of fields in that table.
Drag the field you want from the Field List pane to the table in Datasheet view.
When the insertion line appears, drop the field in position.

The Lookup Wizard starts.
To learn how to complete the Lookup Wizard, see the section Using the Lookup Wizard.

Top of Page

Create a multivalued lookup column in Design view

You can create a lookup column in Design view in addition to Datasheet view. When you work in Design view, you create a lookup column 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.

Click the Microsoft Office Button , and then click Open.
In Open dialog box, select and open the database.
In the Navigation Pane, right-click the table in which you want to add the multivalued lookup column, and then click Design View on the shortcut menu.
Locate the first available empty row in the table design grid.
In the first available empty row, click in the Field Name column, and then type a field name for the multivalued lookup column.
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.
On the first page of the Lookup Wizard, indicate whether you want to base the lookup column on the values in a table or a query, or on a list of values that you type.

The most common type of lookup column is one that displays values looked up from a related table or query.
To learn how to complete the Lookup Wizard, see the section Using the Lookup Wizard.

When you click Finish, a lookup column 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 column, click the Lookup tab.

Top of Page

Using the Lookup Wizard to create multivalued lookup columns

The Lookup Wizard starts in the following cases: when you create a lookup column in Datasheet view, when you drag a field from the Field List pane to a table that is opened in Datasheet view, and in Design view when you select Lookup Wizard in the Data Type column. The wizard walks you through the steps needed to create a lookup column and automatically sets the appropriate field properties to match your choices. The wizard also creates table relationships and indexes, where needed, to support the lookup column.

On the first page of the wizard, you must decide whether to base the lookup column on a table or a query, or on a list of values that you enter. Most of the time, if your database is properly designed and your information is divided into subject based tables, you should choose a table or query as the source of data for the lookup column.

Create the multivalued lookup based on a table or query
1. In the Lookup Wizard, click I want the lookup column 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 column is created whose field properties are set based on the choices you made in the Lookup Wizard.
9. To save the table, click the Microsoft Office Button , and then click Save.
  
  Keyboard shortcut CTRL+S
Create the multivalued lookup based on values 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.
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 column.
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.
  
  When you click Finish, a lookup column is created whose field properties are set based on the choices you made in the Lookup Wizard.
8. To save the table, click the Microsoft Office Button , and then click Save.
  
  Keyboard shortcut CTRL+S
After you create the multivalued field, it appears as a check box drop-down list when you view the table in Datasheet view.

Top of Page

Understanding the bound value and the display value in a multivalued lookup column

Access has supported single-valued Lookup fields for a number of versions. Multivalued Lookup fields are new to Office Access 2007. The purpose of a Lookup field is to replace the display of a number such as an ID (or other foreign key value) with something more meaningful, such as a name. For instance, instead of displaying a contact ID number, Access can display a contact name. The contact ID number is the bound value. The contact ID number is automatically looked up in a source table or query and replaced with the contact name. The contact name is the display value.

As with a single-valued Lookup field that has a display value and a bound value, a multivalued Lookup field has display values that appear in the user interface and bound values that are stored in the table. For each entry in the multivalued field, a display value is "looked up" based on the bound value.

This means that Access often displays a looked-up display value that is not the same as the bound value that is stored in the field. For example, a set of employee ID values from a query named Contacts Extended might be stored in a field named AssignedTo. These employee ID values are the bound values. 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 contact names are the display values.

A datasheet with display values versus a datasheet with bound values

1. The display values.

2. The bound values.

It's important to understand the distinction between a multivalued Lookup field's display values and its bound values. The display values are automatically shown in Datasheet view by default. 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.

Top of Page

Introducing the Lookup field properties

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

Click the Microsoft Office Button , and then click Open.
In Open dialog box, select and open the database.
In the Navigation Pane, right-click the table in which you want to add the lookup column, and then click Design View on the shortcut menu.
Click the lookup column's name in the Field Name column.
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 column. Note that when you use the Lookup Wizard to create a lookup column, 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 column.
Row Source Type	Choose whether to fill the lookup column 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 column. 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 column. 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 column. 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 column.
List Width	Specify the width of the control that appears when you display the lookup column.
Limit To List	Choose whether you can enter a value that isn't in the list.
Allow Multiple Values	Specify whether the lookup column employs a multivalued field and allows multiple values to be selected.
Allow Value List Edits	Specify whether you can edit the items in a lookup column 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 column 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 column is determined by the Bound Column property. The display value in a lookup column 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 lookup column for a multivalued field

To change the design of a multivalued lookup column, you must edit the Lookup field properties in Design view, with one exception. If the lookup column is based on a single column value list and the Allow Value List Edits property is set to Yes, you can change the list of values in Datasheet view or Form view by right-clicking the multivalued lookup column and then clicking Edit List Items.

Edit the value list of a multivalued lookup column 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.

Click the Microsoft Office Button , and then click Open.
In the Open dialog box, select and open the database.
In the Navigation pane, double-click the table that contains the multivalued field.

The table opens in Datasheet view.
Right-click any cell in the multivalued lookup column.
Click Edit List Items.
Change the value list and then click OK.

Change from a value list based multivalued lookup column to a table or query based multivalued lookup column

First, delete the existing value list-based multivalued field.
1. Click the Microsoft Office Button , and then click Open.
2. In the Open dialog box, select and open the database.
3. Open the table in Design view.
4. Select the multivalued field in the design grid.
  
  To select the field, click the field's row selector.
5. On the Design tab, in the Tools group, click Delete Rows.
  
  A confirmation prompt appears.
6. When the confirmation prompt appears, click Yes to delete the field and all the data it contains. To cancel, click No.
Now, create the new multivalued field.

To create a new multivalued field, see the section Create a multivalued lookup column in Datasheet view.

Change the design of a multivalued lookup column

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

Top of Page

Microsoft Office Tutorials

Saturday, November 11, 2017