databasedev.co.uk - database solutions and downloads for microsoft access

Guide to multivalued fields in Access 2007

In the previous Microsoft Office Access versions, as well as in most database management systems, you can store only a single value in a field. But in Microsoft Office Access 2007 you can create a field that holds multiple values. These are appropriate for certain situations, including when you use Office Access 2007 to work with information stored in a Microsoft Windows SharePoint Services 3.0 list.

Multivalued Fields: Definition

Let us assume you need to assign more than one freelancer (or employee) to perform a certain task. To do that you can create a multivalued field that allows you to select the people from a list.

Figure 1 Two freelancers chosen for the project

When you click the combo box, you can tick the checkbox near the name of the freelancer you want to choose. When done, click OK to submit the choices.

Figure 2 Choose the freelancer by ticking the checkbox

The idea behind using multivalued fields in Access 2007 is to make it easy to support those instances when you need to select and store more than one value per field, without having to create advanced database design. You can also use multivalued fields for integration with Windows SharePoint Services because SharePoint lists support such fields.

You might wonder why Access 2007 allows you to store multiple values in a field. The database engine doesn't actually store values in a single field, but the values are actually stored independently and managed in hidden, system tables. But it’s all done by Access and you only see the result: a single field with multiple values, easy to use.

When to use Multivalued Fields

Consider using multivalued fields when:

• you need to store a multiple valued selection from a small list; in this case you can create a multiple valued field using the Lookup Wizard in Table Design View;
• you need to export an Access table to a SharePoint site which uses the multivalued choice or lookup fields available in Windows SharePoint Services; also needs to be done in Access 2007, creating a multivalued field in table design view and then exporting it to a SharePoint list;
• you want to move an Access database to a SharePoint site; also done in table design view and then move it to SharePoint;
• you want to create a link to a SharePoint list.

Note: make sure the database will not be moved to Microsoft SQL Server at a later date, because it doesn't support mutivalued fields and additional design and conversion will be needed.

How to Create a Multivalued Field

Open the database by clicking the Microsoft Office Button, then Open and select the database to open. In the Navigation Pane, double click the table in which you want to create a multivalued field.

Now, on the Datasheet tab, go to the Fields & Columns group and click on Lookup Column.

Figure 3 Lookup Column

This will open the LookUp Wizard.

Figure 4 First Step in LookUp Wizard

Choose to lookup the values in table or query –the most used option- or to type the list of values you need to use, then choose the table/query where to get the information from.

Figure 5 Choose the table/query

Now, choose the fields from the table/query that you need to use.

Figure 6 Choose the fields

Pay attention to the last step, when you need to tick "Allow Multiple Values" for the new lookup column you have created. Otherwise, you won’t be able to use the multivalued field.

Figure 7 Choose to allow multiple values

The same Lookup Wizard can be used to create a multivalued field directly from Design View when editing a table. In Design View, type the Field Name and choose Lookup Wizard in the Data Type column. The result is the same as before.

Figure 8 Creating a Lookup Column directly from Design View

If you want to create a lookup field based on the values you enter, first choose "I will type in the values that I want" when the LookUp Wizard opens, then press Next. Now enter the number of columns you need and the values. When finished click Next. When asked "Do you want to store multiple values for this lookup?", select the Allow Multiple Values check box. Then click Finish. The same result can be obtain if you use the Lookup Wizard in Design View.

Using a Multivalued Field in a Query

Open the database, then on the Create tab, in the Other group, click on Query Design.

Figure 9 Query Design

In the Show Table dialog box click the table that contains the multivalued field and then click "Add".

Figure 10 Choose the table which contains the multivalued field

Drag the fields you want to use in the query.

Figure 11 Drag the fields in the query

Now test the query. In the Design tab, the Results Group, click Run.

Figure 12 The results