Create or delete a multivalued field

In most database systems you can store only a single value in a field. But in Access, you can also create a field that holds multiple values (up to 100). For example, you want to track employees working on issues. One employee can own several issues, and each issue can have several employee working on it. This kind of data structure is called a many-to-many relationship. An Access multivalued field is also useful when you work with a SharePoint list that uses a Choice column.

 

What is a multivalued field?

Suppose you have an issue to assign to one of your employees, but you decide that you need to assign it to more than one employee. You can create a multivalued field that lets you select the employees from a list.

When you click the Multi-select Combo Box arrow, check boxes appear to indicate your choices.

You can check or uncheck items in the list and then click OK to save your choices. The selected items are stored in the multivalued field, and are separated by commas (by default) when displayed.

The idea behind multivalued fields is to make it easy to support those instances where you want to select and store more than one choice, without having to create a more advanced database design. Behind the scenes, Access handles this for you, automatically separating the data and bringing it back together again to surface the values in one field. An Access multivalued field models a many-to-many relationship.

Important   When you migrate an Access database to SQL Server, the Access multivalued field is converted to SQL Server as an ntext field that contains the delimited set of values. Because SQL Server does not support a multivalued data type that models a many-to-many relationship, additional design and conversion work might be needed.

Create a multivalued field

1.      Open a table in Design View.

2.      In the first available empty row, click in the Field Name column, and then type a field name.

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

Note    The Lookup Wizard creates three types of lists depending on the choices you make in the wizard: a lookup field, a values list field, and a multivalued field.

4.      Carefully follow these steps in the wizard:

a.       On the first page, select I will type in the values that I want, and then click Next.

b.      On the second page, keep 1 column selected, enter several values, one in each row under the column header, and then click Next.

c.       On the third page, under Do you want to store multiple values for this lookup?, select the Allow Multiple Values check box, and then click Finish.

5.      Save your changes.

Resize a Multi-select Combo Box control

1.      Open the form or report in Design View or Layout View.

2.      Click the Multi-select Combo Box control, point to one of the resize handles, and drag the edge of the control until it reaches the height or width you want:

Access moves any adjacent controls on the form or report downward or to the left or right automatically.

3.      Save your changes.

Update the properties of a multivalued field

When you use the Lookup Wizard to create a lookup field, the Lookup field properties are set for you. To change the design of the multivalued field, set it's Lookup properties.

1.      Open a table in Design View.

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

3.      Under Field Properties, click the Lookup tab.

4.      Set the Display Control property to Combo Box to see all available properties.

 

Delete a multivalued field

Important    When you delete a multivalued field that contains data, you lose that data permanently — you cannot undo the deletion. For that reason, you should back up your database before you delete any table fields or other database components.

Delete from Datasheet view

1.      Open the table in Datasheet View.

2.      Locate the multivalued field, right-click the header row (the name), and then click Delete Field.

3.      Click Yes to confirm the deletion.

Delete from Design view

1.      Open the table in Design View.

2.      Click the row selector next to the multivalued field, and then press DELETE, or right-click the row selector and then click Delete Rows.

3.      Click Yes to confirm the deletion.