Office 2007 Bible

Adding Fields from More Than One Table

You add fields from more than one table to the query in exactly the same way as when you’re working with a single table. You can add fields one at a time, multiple fields as a group, or all the fields from a table.

Caution

If you type a field name in an empty Field cell that has the same name in more than one table, Access enters the field name from the first table that it finds containing the field name.

If you select the field from the drop-down list in the Field cell, you see the name of the table first, followed by a period and the field name. For example, the ProductID in tblSalesLineItems is displayed as tblSalesLineItems.ProductID. This helps you select the right field name. Using this method, you can select a common field name from a specific table.

The easiest way to select fields is still to double-click the field names in the top half of the Query Designer. To do so, you may have to resize the Field Lists to see the fields that you want to select.

Viewing the table names

When you’re working with two or more tables, the field names in the QBE grid can become confusing. You may find yourself asking, for example, just which table the field is from.

Access automatically maintains the table name that is associated with each field displayed in the QBE grid. Figure 36-21 shows the Query Designer with the name of each table displayed under the field name in the QBE grid.

Figure 36-21. The QBE grid with table names displayed. Notice that ...