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 it shows all four table names.
After you add fields to a query, you can view the returned records at any time. Figure 36-22 shows the data returned by the query in Figure 36-21.
Figure 36-22. Datasheet view of data from multiple tables. This resulting recordset, from the query, contains 84 records.
Adding multiple fields
The process of adding multiple fields in a multi-table query is identical to adding multiple fields in a singletable query. When you’re adding multiple 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.
You can 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 with the mouse.
Caution
Selecting the * does have one drawback: You cannot 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 *), the query displays the field twice—once for the * field and a second time for the criterion field. Therefore, you may want to deselect the Show cell of the criterion field.