Microsoft Office Tutorials and References
In Depth Information
Creating a Lookup Field
When you select the Allow Multiple Selections check box in the Lookup
Wizard, your lookup list looks like Figure 5-6. Access creates a hidden join
table to store the many-to-many relationship between the two tables — in
this case, the Employee table and the Role table — involved in the lookup.
In this case, the multiple-value lookup is used to define multiple employee
roles. If Matt is both a manager and a systems administrator, a
multiplevalue field lets you define him as both. You can run a query to find all
managers and see Matt in the list, or to find all systems administrators and see
Matt in that list also.
Allowing multiple selections in a lookup field can be a tremendously
convenient feature if it’s used correctly. You shouldn’t use it when you need
to add other information about the choice. In the Order Details table, for
example, you don’t want the item ordered to be a multiple-value field, even
though a customer may in fact order multiple items. Instead, you need each
item ordered to have its own record so that you can record how many of the
items the customer wants and so that you can do calculations with that data
down the line (that is, calculate the order total).
Access doesn’t really store multiple values in one field, which would break
the laws of good database design. Instead, it creates a hidden join table.
This intermediate table is the join table in the many-to-many relationship
between the two tables. Although it’s more work to set up the join table
yourself, in many situations, it’s the right choice to make. Then you can
create a form to make data entry as quick as it would be with a
multipleselection lookup field.
Be aware that if you ever want to upsize your database to SQL Server, the
multiple-value feature doesn’t convert well, but if you’re staying in Access or
SharePoint, it’s a great feature when used correctly. (We cover using Access
with SQL Server and SharePoint in Book IX.)