Using lookup and multivalued fields in queries

In Access, you can create a multivalued field that holds multiple values (up to 100). You can also create a Lookup field that displays a user friendly value bound to a value in another data source. When you query a Lookup or multivalued field, there are unique considerations. For more information, see Create or delete a multivalued field and Create or delete a lookup field.

In this article

• View the bound value of a Lookup field in a query

• Use the display value for a Lookup field in a query

• Use a multivalued field in a query

• Add criteria to a multivalued field in a query

• Add multiple criteria to a multivalued field in a query

• Grouping and counting a multivalued field in a query

• Use an Append query with a multivalued field

• Use an Update query with a multivalued field

• Use a Delete query with a multivalued field

View the bound value of a Lookup field in a query

The display value of a Lookup field is automatically shown in query datasheet view by default. When you create a query, you can override this behavior so that the bound value is shown instead. In this example, suppose you want to see the bound value of a Lookup field in a Headquarters table that "looks up" a New England state name.

1. Open the query in Design View.

2. In this example, add Headquarters and NewEngland tables.

   The two tables should be joined.

   

   For more information, see Join tables and queries.

3. Drag the a field along with the Lookup field to the query design grid. In this example, add the City and NEState field.

4. Click the Lookup field, and then on the Design tab, in the Show/Hide group, click Property Sheet. In this example, use the NEState field.

5. In the property sheet, select the Lookup tab, and then in the Display Control property, select Text Box.

   

Result   Now when you view the query in Datasheet View, you see the corresponding bound value of the display value.

Top of Page

Use the display value for a Lookup field in a query

When you create a Lookup field, a join is created between the Bound value and the Display value. Although you cannot directly use the Display value in a query, you can use the corresponding value from the other data source. In this example, suppose you want to use the display value of the Lookup field, NEState, in the Headquarters table in a query as criteria.

1. Open the query in Design View.

2. In this example, add the Headquarters and NewEngland tables.

   The two tables should be joined. For more information, see Join tables and queries.

3. Drag the fields that you want to use to the query grid. In this example, drag City from the Headquarters table to the first column, NEState from the Headquarters table to the second column, and StateName from the NewEngland table to the third column.

4. Clear the Show check box of the StateName in the query grid.

5. In the query grid, under StateName, in the Criteria row, enter Vermont.

   

   The query criteria is based on the StateName column, which of course is the same value as the Display value, NEState, but is not shown in Datasheet View.

6. On the Design tab, in the Results group, click Run.

Result    Only the row containing Vermont is shown.

Top of Page

Use a multivalued field in a query

When you display a multivalued field in a query, you can display the complete multivalued field containing all of the values separated by commas on one row, or flattened data with a separate row for each value. For example, suppose you have an Issues table that contains a Title field for each issue and an AssignedTo multivalued field to assign issues to people.

Display all values in a multivalued field in one row

1. Open the query in Design View.

2. In this example, add the Issues table.

3. Drag the fields to the query design grid. In this example, drag the Title field and the AssignedTo multivalued field.

4. On the Design tab, in the Results group, click Run.

Result    As you might expect, one column displays the Title field and the second column displays the AssignedTo multivalued field:

Display each value of a multivalued field in one row (the flattened view)

1. Open the query in Design View.

2. In this example, add the Issues table.

3. Drag the fields you to the query grid. In this example, drag the Title field and the AssignedTo.Value multivalued field.

   The format, <Fieldname>.Value, appends the Value property as the string .Value to the AssignedTo field.

4. On the Design tab, in the Results group, click Run.

Result    The data is flattened such that the Title field is repeated and each value in the AssignedTo multivalued field is displayed in a corresponding row:

Top of Page

Add criteria to a multivalued field in a query

The placement of the same criteria in the query grid in different grid columns has a big impact on the results of your query.

Add criteria that displays all the values in a multivalued field in one row

Access first creates a result set and then adds the criteria.

1. Open the query in Design View.

2. In this example, add the Issues table.

3. Drag the fields you to the query grid. In this example, drag the Title field, the AssignedTo multivalued field, and AssignedTo.Value multivalued field.

4. Clear the Show check box of the AssignedTo.Value in the query grid.

5. In the query grid, under AssignedTo.Value, in the Criteria row, enter "NOT "David Hamilton".

   

   It's helpful to see the SQL view:

   SELECT Title, AssignedTo FROM Issues WHERE NOT AssignedTo.Value = "David Hamilton";

6. On the Design tab, in the Results group, click Run.

Result    The three issues not assigned to David Hamilton are displayed in a default value.

Add criteria that displays each value in a multivalued field in one row (the flattened view)

Access first creates a flattened result set and then adds the criteria.

1. Open the query in Design View.

2. In this example, add the Issues table.

3. Drag the fields you to the query grid. In this example, drag the Title field and the AssignedTo.Value multivalued field.

4. In the query grid, under AssignedTo.Value, in the Criteria row, enter NOT "David Hamilton".

   

   It's helpful to see the SQL view:

   SELECT Issues.Title, AssignedTo.Value FROM Issues WHERE NOT AssignedTo.Value = "David Hamilton"

5. On the Design tab, in the Results group, click Run.

Result    Each issue not assigned to David Hamilton is displayed in a flattened value.

Top of Page

Add multiple criteria to a multivalued field in a query

Sometimes you need to search for a match on more than one value inside a multivalued field. For example, suppose you want to see those issues in which both "Kelly Rollin" and "Lisa Miller" are among the values in the AssignedTo field

1. Open the query in Design View.

2. In this example, add the Issues table.

3. Drag the fields you to the query grid. In this example, drag the Title field and the AssignedTo multivalued field.

4. In the query grid, under AssignedTo, in the Criteria row, enter "Kelly Rollin" AND "Lisa Miller".

5. On the Design tab, in the Results group, click Run.

Result    Those two issues that are assigned to "Kelly Rollin" and "Lisa Miller" are displayed.

Top of Page

Grouping and counting a multivalued field in a query

To perform calculations, grouping, and sorting of values stored in a multivalued field, use the <Fieldname>.Value field. For more information on group queries, see Count data by using a query.

To count the number of issues assigned to each person

1. Open the query in Design View.

2. In this example, add the Issues table.

3. Drag the fields that you want to use to the query grid. In this example, drag AssignedTo.Value to the first column and Title to the second column.

4. On the Design tab, in the Show/Hide group, click Totals.

   The Total row appears in the query grid. Group By appears by default in the Total cell under each field in the query.

5. In the query grid, under Title, in the Total row, click Count.

6. On the Design tab, in the Results group, click Run.

Result    The count of issues assigned per person is displayed in a flattened view.

To count how many people are assigned to each issue

1. Open the query in Design View.

2. In this example, add the Issues table.

3. Drag the fields that you want to use to the query grid. In this example, drag Title to the first column and drag AssignedTo to the second column.

4. On the Design tab, in the Show/Hide group, click Totals.

   The Total row appears in the query grid. Group By appears by default in the Total cell under the Title field in the query. Expression appears by default in the Total cell under the AssignedTo field because you cannot perform a Group By operation directly on a multivalued field, only a <Fieldname>.Value field.

5. In the query grid, under AssignedTo, in the Total row, click Count.

6. On the Design tab, in the Results group, click Run.

Result    The count of people assigned per issue is displayed in a flattened view.

Top of Page

Use an Append query with a multivalued field

You can insert a single value into a multivalued field by using an Append query. For example, suppose you want to add "Tom Michaels" to the AssignedTo multivalued field in the Issues table.

Note   This the only type of Append query that works with a multivalued field.

1. Open the query in Design View.

2. Add the Issues table.

3. On the Design tab, click Append.

4. In the Append dialog box, select Issues, and then click OK.

5. In the Append To row of the Design Grid, select AssignedTo.Value.

6. In the Field row of the Design Grid, enter "Tom Michaels".

7. To limit the append operation to specific issues, add a field to the Design grid, such as Title, remove Title from the Append To row, and then enter criteria such as "Issue 3".

8. On the Design tab, in the Results group, click Run.

   Access might ask you to confirm whether to append the selected row. Click Yes to insert the row, or click No to cancel.

Result    "Tom Michaels" is now added to the AssignedTo field for Issue 3.

Important    You cannot use an Append query that references a table that contains a multivalued field. For example, the following query is not valid:

INSERT INTO [NewIssues] ( ID, Title, AssignedTo ) SELECT Issues.ID, Issues.Title, Issues.AssignedTo FROM Issues;

Top of Page

Use an Update query with a multivalued field

You can use an Update query to change a single value in a multivalued field to another value. In this example, you want to update the AssignedTo multivalued field to replace "Kelly Rollin" with "Lisa Miller."

1. Open the query in Design View.

2. Add the Issues table.

3. On the Design tab, click Update.

4. Drag AssignedTo.Value to the Query grid.

5. In the Update To row, enter "Lisa Miller".

6. In the Criteria row, enter "Kelly Rollin".

7. On the Design tab, in the Results group, click Run.

   Access might ask you to confirm whether to append the selected row. Click Yes to insert the row, or click No to cancel.

Result    Lisa Miller replaces Kelly Rollin in the AssignedTo field for all corresponding issues.

Top of Page

Use a Delete query with a multivalued field

When you work with a table that contains a multivalued field, you can use a delete query to delete records that contain a particular value in a multivalued field, or to delete a particular value from a multivalued field in all of the records in the table. In the following example, suppose you want to delete "David Hamilton" from the Issues table.

Important    When you use a delete query to delete a multivalued field that contains data, you lose that data permanently — you cannot undo the deletion. For that reason, you should back up your database before you delete any table fields or other database components.

To delete a particular value from a multivalued field in all records

1. On the Create tab, in the Queries group, click Query Design.

2. Add the Issues table.

3. Open the query in Design View.

4. On the Design tab, click Delete.

5. In the Criteria row, enter "David Hamilton".

6. On the Design tab, in the Results group, click Run.

   Access might ask you to confirm whether to delete the records. Click Yes to delete the records, or click No to cancel.

Result    David Hamilton is removed from for all corresponding issues.

Delete records that contain a particular value in a multivalued field

1. On the Create tab, in the Queries group, click Query Design.

2. Add the Issues table.

3. Open the query in Design View.

4. On the Design tab, click Delete group.

5. On the Design tab, in the Results group, click the arrow under View and then click SQL View.

6. Enter the following SQL statement:

   DELETE FROM Issues WHERE (((Issues.AssignedTo.Value)="David Hamilton"));

   Note    In this case, you can only use an SQL statement, not the Design grid. If you switch to the Design grid view, Access adds an asterisk (*) after the DELETE statement, which you should remove from SQL view.

7. On the Design tab, in the Results group, click Run.

   Access might ask you to confirm whether to delete the records. Click Yes to delete the records, or click No to cancel.

Result    All issues where David Hamilton is assigned are removed.

Top of Page