Working with Multi-Value Lookup Fields

In Chapter 1, “What Is Microsoft Access?," we introduced you to the concept of complex data. Access 2007 includes a new feature called Multi-Value Lookup Fields to handle complex data. The purpose of lookup fields, as you just learned, is to display one value in a field but actually store a different value. For example, a lookup field could store the company ID in a field for an invoice, but display the company name to the user for easier data entry on a form or to show the name on a printed invoice report. Lookup fields in this scenario take the guesswork out of trying to remember a specific company ID number. Multi-Value Lookup Fields take this concept a step further by allowing you to store multiple values in a single lookup field. When you define a field as a Multi-Value Lookup Field, Access provides a special control in the Datasheet view of the table similar to a combo box to display the list of valid values. When you drop down the combo box list, you’ll see what looks like a list box that has a check box next to each of the available value choices. Selecting the check box next to one or more of the values stores the selected values in the field.

Figure 5–37 shows an example of a Multi-Value Lookup Field in the Conrad Systems Contacts database. Open the Contacts.accdb database and then open the tblContacts table in Datasheet view. Any specific contact could be one or more contact types. The Contact Type field is designated as a Multi-Value Lookup Field, so the user can select from any of the contact types in the database and mark them as related to the current record. In Figure 5–37 you can see that John Viescas is both a developer and a distributor. By selecting the check boxes next to the available contact types, you tell Access to store multiple values for this single record. Notice that after you tab away from this field, Access separates the values with commas.

Figure 5–37: A Multi-Value Lookup Field control allows you to select more than one value for that field.

Access also provides the list box control you see in a table in Datasheet view on a form in Form view. Close the tblContacts table and then open the frmContactsPlain form in Form view. In Figure 5–38 you can see the Contact Type field, which displays an arrow on the right side. Clicking the arrow drops down the list with the available choices of contact types.

Figure 5–38: Access also provides a Multi-Value Lookup Field control in the frmContactsPlain form of the Conrad Systems Contacts database.

To set up a Multi-Value Lookup Field you must set the properties in the table in Design view. Close the frmContactsPlain form and then open the tblContacts table in Design view. (Because this is a linked table, Access will warn you that you cannot modify the design. Click Yes in the warning dialog to open the table design and view the field properties.) Click the ContactType field and then click the Lookup tab under Field Properties to see the settings as shown in Figure 5–39. The Allow Multiple Values property has been set to Yes, which tells Access that it can store multiple values in this field.

Figure 5–39: Set the Allow Multiple Values property to Yes to enable this field as a Multi-Value Lookup Field.

How Do Multi-Value Lookup Fields Maintain Data Normalization Rules?

If you are familiar with data normalization rules, you might be asking yourself how it is possible to store multiple values in a single field and still follow normalization rules. Under the covers and hidden from the standard user interface, Access 2007 actually creates a many-to-many relationship with a hidden join table. All the work of creating this join table and establishing the relationship rules is handled by Access when you set the Allow Multiple Values property to Yes or choose to allow multiple values in the Lookup Wizard. To ensure that only possible related values can be entered in the Multi-Value Lookup Field, Access displays a combo box or list box control containing only the valid related values for data entry. These Multi-Value Lookup Fields allow for better integration with Microsoft Windows SharePoint Services (version 3) complex data structures.

However, you cannot upsize any table that has a Multi-Value Lookup Field to SQL Server. We converted the ContactType field to a standard single-value field to be able to upsize it correctly. Although Multi-Value Lookup Fields can help novice developers create applications that deal with complex many-to-many relationships in a simple way, we recommend that you learn to create such relationships properly when you need them in your database using the appropriate linking table. We wish that Microsoft had made the special multi-value control available for normal many-to-many relationships in Access 2007, but that is not the case. The development team at Microsoft has promised to provide further enhancements in future releases.