Lookup Field and Querying Multi-Value Fields
Understanding the bound value and the display value in a lookup field
In Access 2007 and Access 2010, a multivalued field is a lookup field. A lookup field is a field in a table whose value is retrieved from another table or query, or from a value list. Access has supported single-valued lookup fields for a number of versions. Multivalued lookup fields are new to Access since Access 2007. The purpose of a lookup field is to replace the display of a number such as an ID (or other foreign key value) with something more meaningful, such as a name. For example, instead of displaying a contact ID number, Access can display a contact name. However, the contact ID number is the bound value. It is automatically looked up in a source table or query and replaced with the contact name. The contact name is the display value.
As with a single-valued lookup field that has a display value and a bound value, a multivalued lookup field has display values that appear in the user interface, and bound values that are stored in the table. For each entry in the multivalued field, a display value is looked-up, based on the bound value.
It’s important to understand the distinction between a lookup field’s display value and the bound value. The display value is automatically shown in datasheet view by default. However, the bound value is what is stored, it is what you use in query criteria, and what Access uses by default in joins with other tables.
For example, say you have a table with field that look’s up a person’s name and that field is called FirstName. The chances are that the person’s name is the display value whereas their ID number is the bound value. If you wanted to create a query and placed the FirstName field in the design grid, then any criteria would have to be based upon the bound value and not what is seen. Therefore, you would enter different ID numbers rather than names. This can be very confusing and no use to end users who have never seen the ID numbers.
In order to create a query that lets you enter names for criteria, you would need to include the table that is used for the lookup, and place any name criteria under that table’s FirstName field.
The same concept of bound values and display values applies to multi-value fields.
As an example look at this picture where there is a table called Events. The People field is a multi-value field that uses a lookup in the Names table. To enter values in the Events table the user can select names from a drop-down list.
To create a query that displays entries from the Events table according to someone’s FirstName or LastName, the user needs to add in the Names table and add the relevant fields in the grid.