Tag Archives | multi-value fields

Querying Multi-Value Fields in Access

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.

Multi-Value Fields

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.

What is a Relational Database?

A relational database holds its data in tables. These tables may be linked together, using common values within them. The tables are not permanently linked together. Each table is an independent data store – but the database system is capable of linking the data together – when required.

Generally speaking, each table contains data relating to a particular subject, for example, customer details could be held in one table, and the orders placed by the customers in a separate table. If you wished to see all the orders for a particular customer, the tables could be linked.


Failure to use normalised tables may result in a loss of data integrity.  To this end, you should examine your tables and proceed through the normalisation process to try to achieve third normal form (3NF).

First Normal Form – 1NF

A table is in 1NF if and only if every non-primary key is contains only one entry.

Second Normal Form – 2NF

This is relevant for tables with multi-field primary keys. The table is in 2NF if it is in 1NF and every non-primary key is only dependent on the whole primary key. In other words, you must not be able to determine values in the rest of the table from any subset of the primary key.

Third Normal Form – 3NF

A table is in 3NF if it is in 2NF and every non-primary key is dependent only on the primary key. Therefore, one non-key field must not be dependent upon another non-key field.

Relational Databases and Normalisation

Relational database resolve the above normalisation issues by having separate tables for details that could be repeated within tables.

Microsoft Access 2007 and 2010

Since Access 2007 there has been a new data type in Access tables, which is the multi-value field. This new data type was introduced to allow Access to work with SharePoint. The multi-value field in theory breaks the normalisation rules discussed earlier. However, behind the scenes Access is still applying the normalisation rules by using hidden interim tables. However, writing queries for access databases that use tables with multi-value fields is fraught with problems. I’ll take a look at these problems in a future blog.