Guide to multivalued fields
In most database management systems you can store only a single value in a field, including earlier versions of Microsoft Access. In Microsoft Office Access 2007, you can also create a field that holds multiple values, such as a list of categories to which you have assigned an issue. Multivalued fields are appropriate for certain situations, such as when you use Office Access 2007 to work with information stored in a Windows SharePoint Services 3.0 list, and that list contains a field that uses one of the multivalued field types available in Windows SharePoint Services.
This topic covers multivalued fields in Office Access 2007 and in Windows SharePoint Services, how to create a multivalued field and when to use one, and how to employ a multivalued field in a query.
In this article
What is a multivalued field?
Suppose you have a task to assign to one of your employees or contractors, but you decide that you need to assign it to more than one person. In Office Access 2007, you can create a multivalued field that lets you select the people from a list.
When you click the combo box, check boxes appear selected to indicate your choices. You can check or uncheck items in the list and then click OK to submit your choices.
The selected people 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. Multivalued fields are also important for integration with Windows SharePoint Services because SharePoint lists also support multivalued fields.
You might wonder why Office Access 2007 allows you to store more than one value in a field, when most relational database management systems forbid this. The answer is that the database engine in Office Access 2007 doesn't actually store the values in a single field. Even though what you see and work with appears to be a single field, the values are actually stored independently and managed in hidden, system tables. The Access database engine handles this for you, automatically separating the data and bringing it back together again to surface the values in one field.
Technically speaking, an Access multivalued field models a many-to-many relationship. For instance, consider an events table where you assign responsibility for each event to one or more of your employees. Suppose you create a multivalued field named "AssignedTo" for assigning events to employees. The relationship between events and employees is many-to-many. That is, you can have many employees assigned to any one event, and you can also have any one employee assigned to many events.
Multivalued fields in Windows SharePoint Services
Windows SharePoint Services 2.0 supports a Choice column that lets you select and store more than one choice in a single field. In Windows SharePoint Services 3.0, you can also create a Lookup field that lets you select and store more than one choice in a single field. For Office Access 2007 to integrate with Windows SharePoint Services and support its field types, Office Access 2007 implements support for multivalued fields.
When you link to a SharePoint list that employs one of these multivalued data types, Office Access 2007 automatically creates and uses an Access multivalued type locally.
When you export an Access table to a SharePoint list and the Access table contains a multivalued field, the field becomes one of the multivalued field types available in Windows SharePoint Services. Similarly, when you move an Access database to a SharePoint site and the database contains Access tables that contain multivalued fields, each of those multivalued fields becomes one of the multivalued field types of Windows SharePoint Services.
When to use a multivalued field
You should consider creating a multivalued field when you want to:
-
Store a multiple valued selection from a list of choices, and that list of choices is relatively small.
You create the multivalued field in Office Access 2007 by using the Lookup Wizard in table Design view.
-
Export an Access table to a SharePoint site so that it employs the multivalued choice or lookup fields available in Windows SharePoint Services.
You create the multivalued field in Office Access 2007 by using the Lookup Wizard in table Design view. You then export the table to a SharePoint list.
-
Move an Access database to a SharePoint site so that it employs the multivalued choice or lookup fields available in Windows SharePoint Services.
You create the multivalued field in Office Access 2007 using the Lookup Wizard in Table Design view. You then click the Move to SharePoint command on the External Data tab.
-
Link to a SharePoint list that contains a multivalued Choice or multivalued Lookup field.
The Office Access 2007 multivalued field is automatically created for you and mapped to the Windows SharePoint Services field when you create the linked table.
In addition to the preceding scenarios, you might also want to use a multivalued field when you are relatively sure your database will not be moved to Microsoft SQL Server at a later date. A Office Access 2007 multivalued field is upsized to SQL Server as a memo (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.
How to create a multivalued field
When you open a table, the table opens in Datasheet view. You can add a multivalued lookup column by clicking Lookup Column in the Fields & Columns group on the Datasheet tab. This starts the Lookup Wizard to lead you through the process of creating the lookup column.
-
Click the Microsoft Office Button
, and then click Open. -
In Open dialog box, select and open the database.
-
In the Navigation Pane, double-click the table in which you want to create the lookup column to open it.
-
On the Datasheet tab, in the Fields & Columns group, click Lookup Column.
The Lookup Wizard appears.
-
In the first dialog box of the Lookup Wizard, you must decide whether you want to base the lookup column on the values in a table or query, or on a list of values that you type in.
The most common type of multivalued lookup column is one that displays values looked up from a related table or query.
-
To learn how to complete the Lookup Wizard, see Using the Lookup Wizard. For more in-depth information on how to create a multivalued field, see Add or change a lookup column that lets you store multiple values.
Using the Lookup Wizard
The Lookup Wizard is invoked when you create a lookup column in Datasheet view, when you drag a field from the Field List pane to a table that is opened in Datasheet view, and in design view when you choose Lookup Wizard in the Data Type column. The wizard walks you through the steps needed to create a lookup column, and automatically set the appropriate field properties to match your choices. The wizard also creates table relationships and indexes, where needed, to support the lookup column.
When the wizard appears, you must decide whether to base the lookup column on a table or query, or a list of values that you enter. Most of the time, if your database is properly designed and your information is divided into subject based tables, you will want to choose a table or query to source the lookup column.
-
Create the multivalued lookup based on a table or query
When you drag a field from the Field List pane to create a multivalued field, the first two steps in the following procedure are automatically done for you, so you begin with the third step.
-
In the Lookup Wizard, click I want the lookup column to look up the values in a table or query, and then click Next.
-
Select a table or query from the list, and then click Next.
-
Under Available Fields, click the field that you want included in your lookup.
-
Click the greater-than button (>) to move the field you clicked to the Selected Fields list.
-
Repeat the preceding two steps until you have listed in the Selected Fields list all of the fields that you want included in your lookup. Then click Next.
-
Optionally, select one to four fields on which to sort the lookup items, and then click Next. Click the Ascending button to switch from sorting the lookup items in ascending order to sorting the lookup items in descending order. Note that the name of the button switches to Descending. Click the button again to switch back. Click Next to continue.
-
Adjust the width of the columns in your lookup field, if necessary, then click Next.
-
Under Do you want to store multiple values for this lookup?, select the Allow Multiple Values check box.
Note: This check box must be selected to enable storing multiple values.
-
Click Finish.
When you click the Finish button, a lookup column is created whose field properties are set based on the choices you made in the Lookup Wizard.
-
To save the table, click the Microsoft Office Button
, and then click Save.
-
-
Create the multivalued lookup based on values you enter
-
In the Lookup Wizard, click I will type in the values that I want, and then click Next.
-
Enter the number of columns. Then, type each value. To move to the next column or row, press TAB.
-
When you are finished entering values, click Next.
-
If you specified more than one column at step 2, you must choose which column you will use to uniquely identify each value. In the Available Fields box, double-click the column that you want to use to uniquely identify each value.
Note: This step does not appear if you did not specify more than one column at step 2.
-
In the Lookup Wizard, type the label for your lookup column.
-
Under Do you want to store multiple values for this lookup?, select the Allow Multiple Values check box.
Note: This check box must be selected to enable storing multiple values.
-
Click Finish.
When you click the Finish button, a lookup column is created whose field properties are set based on the choices you made in the Lookup Wizard.
-
To save the table, click the Microsoft Office Button
, and then click Save.After you create the multivalued field, it appears as a check box drop-down list when you view the table in Datasheet View.
-
Modify the design of a multivalued field
For information on how to modify the design of a multivalued field, see the article Add or change a lookup column that lets you store multiple values.
How to employ a multivalued field in a query
When you employ a multivalued field in a query, you must decide if you want to retrieve the complete multivalued field containing all of the values separated by commas, or a separate row for each value. For instance, suppose you have an Issues table that contains an AssignedTo field that you use to assign issues to employees. You can construct a query that contains the AssignedTo field like this:
-
Click the Microsoft Office Button
, and then click Open. -
In the Open dialog box, select and open the database.
-
On the Create tab, in the Other group, click Query Design.
-
In the Show Table dialog box, click the table (in this example, "Issues") that contains the multivalued field, and then click Add.
-
Click Close.
-
Drag the fields you want to use to the query grid. In this example, drag the Title field and multivalued field named "AssignedTo" to the query grid.
-
On the Design tab, in the Results group, click Run.
Your query result will look something like this, with one column displaying the issue title and the second column displaying the multivalued field:
Suppose you want to see the AssignedTo multivalued field expanded, so that each AssignedTo value appears in a separate row. To do this, you need to specify the Value property by appending the string ".Value" to "AssignedTo" in the Field row, as shown here in the query grid:
When you specify AssignedTo in the Field row, Access displays all of the values in the multivalued field in just one row when you run the query. However, when you use the Value property, as in AssignedTo.Value, Access displays the multivalued field in expanded form so that each value appears in a separate row. You can create a query to display the individual values like this:
-
Click the Microsoft Office Button
, and then click Open. -
In the Open dialog box, select and open the database.
-
On the Create tab, in the Other group, click Query Design.
-
In the Show Table dialog box, click the table (in this example, "Issues") that contains the multivalued field, and then click Add.
-
Click Close.
-
Drag the fields you want to use (in this example, the multivalued field named "AssignedTo.Value") to the query grid.
-
On the Design tab, in the Results group, click Run.
Your query result will look something like this, with one column displaying the issue title and the second column displaying the AssignedTo.Value:
For more information on using multivalued fields in queries, see Using multivalued fields in queries.
No comments:
Post a Comment