Adding More Than One Table to a Query
Using a query to obtain information from a single table is common; often, however, you need information from several related tables. For example, you may want to obtain a buyer’s name and vehicle type purchased by the contact. This query requires four tables: tblContacts, tblSales, tblSalesLineItems, and tblProducts.
In Chapter 34, you learned the importance of primary and foreign keys and how they link tables together. You learned how to use the Relationships window to create relationships between tables. Finally, you learned how referential integrity affects data in tables.
After you create the tables for your database and decide how the tables are related to one another, you are ready to build multiple-table queries to obtain information from several related tables. The query combines data from multiple tables and presents the data as if it existed in one large table.
The first step in creating a multiple-table query is to add the tables to the Query window:
1. | Create a new query by clicking the Query Design button on the Create tab. |
2. | Select
tblContacts, tblSales, tblSalesLineItems, and
tblProducts
by double-clicking each table’s name in the Show Table dialog box. |
3. | Click the Close button in the Show Table dialog box. |
Note
You can also add each table by highlighting the table in the list separately and clicking Add.
Figure 36-20 shows the top pane of the Query Design window with the four tables you just added. Because the relationships were set at table level, the join lines are automatically added to the query.
Figure 36-20. The Query Design window with four tables added. Notice the join lines are already present.
Note
You can add more tables, at any time, by choosing Query Setup 
Show Table from the Design tab of the Query Design view. Then click Add in the dialog box that appears.