How to use the new multivalue field in Access 2007/2010

If you're hesitant to use the accdb-format multivalue field, read this introduction to the feature. It might help you make your decision.

Access 2007 has a new feature - it can store multiple values in one field. That capability is a bit confusing, since storing multiple values in a field violates normalization (which allows only one value per field).

Multivalued fields allow users to select and store more than one value, or choice, in response to the same question or control. For instance, if several employees are working on the same project, you can store all of their names in the same field. In truth, the user making the choice really won't notice the difference - only the creator will know about the table-level attribute. The database developer won't have to work as hard when designing and creating tables.

Because such a field violates normalization rules, you might ignore it. The fact is, Access doesn't store the values in a single field. To you, it might look that way, but under the hood, Access stores the data in normalized, but hidden, tables - Access is handling normalization for you, by separating and storing the data and then pulling it all together in the user interface. Technically, the multivalue field is actually a many-to-many relationship, but you don't have to do any of the work to create it and you won't actually see that relationship at work.

Developers that have been using Access for years are probably a bit leery of the multivalue field. They might refuse to use it and that's okay. Here are a few guidelines to help you make that decision for yourself:

  • Don't try to use this feature with long lists.
  • Use multivalue fields if you're using SharePoint Services.
  • SQL Server converts a multivalue field to a Memo field, so keep upgrading in mind when making your choice.

Once you decide to use a multivalue field, creating it is simple, as a wizard does most of the work:

  1. Open the table in Design view.
  2. Select a blank row and click Modify Lookups in the Tools group on the (contextual) Design tab. In Office 2007, click Lookup Column in the Fields & Columns group on the Datasheet tab.
  3. In the first pane, specify whether the values exist in a table or query or that you'll type the values yourself. The remaining panes will be dependent on this choice. You'll identify the table and field that contains the values or you'll type them.
  4. In the last pane, be sure to check the Allow Multiple Values option, before clicking Finish.
  5. Click Yes when Access prompts you to save the altered table.

I didn't offer specific step-by-step instructions for completing the wizard because it's easy - you really won't need my help. But, if you do end up needing some assistance, you can find detailed instructions for using the wizard online at Guide to multivalued fields.

The only thing simpler than creating a multivalue field is using it! Honestly, you don't have to do a thing, unless you want to. I used the Form Wizard to create the form below - Access did all the work creating that combo box. I didn't have to do a thing. To use the control, just check the values that apply.

If you open an mdb format database in Access 2007 or 2010, this feature will create a lookup field, not a multivalue field. Be sure to convert the mdb database to the accdb format first. Then, this feature will create a multivalue field. To convert the mdb file, click the File tab, choose Save & Publish, and select Access Database (*.accdb), and click Save As. Click Save and then OK to confirm the conversion.

Do you intend to use multivalue fields? Let us know what you think of this new feature is our latest poll.