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.
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.
Consider using multivalued fields when:
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.
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.
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