Microsoft Access 2010 Bible

Part I: Access Building Blocks

156

FIGURE 4.24

The Datasheet view of data from multiple tables

Adding multiple fields

The process of adding multiple fields in a multi-table query is identical to adding multiple fields in

a single-table query. When you’re adding fields from several tables, you must add them from one

table at a time. The easiest way to do this is to select multiple fields and drag them together down

to the QBE grid.

Select multiple contiguous fields by clicking the first field of the list and then clicking the last field

while holding down the Shift key. You can also select noncontiguous fields in the list by holding

down the Ctrl key while clicking individual fields.

Caution
Selecting the asterisk (*) does have one drawback: You can’t specify criteria on the asterisk column itself. You
have to add an individual field from the table and enter the criterion. If you add a field for a criterion (when
using the asterisk), the query displays the field twice — once for the asterisk field and a second time for the cri-
terion field. Therefore, you might want to deselect the Show cell of the criterion field.

Multi-table query limitations

When you create a query with multiple tables, there are limits to which fields can be edited.

Generally, you can change data in a query’s recordset, and your changes are saved in the underly-

ing tables. The main exception is a table’s primary key — a primary key value can’t be edited if ref-

erential integrity is in effect and if the field is part of a relationship.

To update a table from a query, a value in a specific record in the query must represent a single

record in the underlying table. This means that you can’t update a field that transforms data, such

as combining first and last names. Each field in a transformed recordset usually represents multiple

fields in the underlying tables. There is no way to change the data in a transformed field and have

it reflected in the underlying tables.