Software

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.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

5 comments
jvansanten
jvansanten

In general, I think it's better to do a physical normalization in cases where multi-valued fields would be used. But, I've encountered one situation where that looks fully appropriate. Many tables have status type fields -- which indicate state. These are simply signals which don't have further attributes. I have a table with staff contacts -- which can be 1:many. This works very well. Check the Microsoft Office site for full information on using these fields and also querying.

kwcarpenter
kwcarpenter

The multi value data feature causes a problem in the pivot chart. They truncate to 2 characters. Say the data elements are Red, Green, and Blue. One record has Red, one has Green, and a third has Blue and Red. When displayed in a pivot table, the labels, as well as the data choices for the filter, are now shown as Re, Gr, and Bl..... I have yet to find a reason or a fix for this one.

darkshark643
darkshark643

That's a nice and clear article...I was using it without know that was new. But now that I've to convert my Access db in a MySQL db I've a lot of problems, also with mysql migration toolkit made for MS Access db. Do you think I should utilize the normal relation to avoid the conversion problem? Thank you

SSchapel
SSchapel

Good article, Susan. Thanks. But I had to do a double-take on the date. Wow, this feature has been available in Access for over 4 years now! And the article title and survey questions call it "new"? Might I also suggest a follow-up article on how to manage the multi-value field data in queries and code - that's where it gets a bit tricky and needs some "new" skills.

ssharkins
ssharkins

New to 2007... not new as in brand new to the market. Thank you for the possible topics. I'll definitely consider them!