Office 2007 Bible

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 ...