Creating Multi-Table Queries

The real power of relational databases is to use the related tables to create other Access objects, such as queries. Multi-table queries enable you to pull information from several related tables. You can then use this query to create a report or a form.

The easiest way to create a multi-table query is in the Query Design view. Follow these steps:

1. From the database window (with the Queries icon selected), double-click Create Query in Design View . The Show Table dialog box appears.

2. In the Show Table dialog box (see Figure 19.4), select the related tables that you want to include in the query. For example, using the tables shown in Figure 19.4, you could create a query using the Employees, Departments, and Expenses tables that would show you each employee, the department, and any expenses that the employee has incurred.

   Figure 19.4. Select the tables that will be used to create the multi-table query.

   

3. After you have selected the tables for the query, click Close to close the Show Table dialog box. The tables and their relationships appear at the top of the Query Design window.

4. Add the fields to the Query grid that make up the query. The fields can come from any of the tables that you have included in the query. Figure 19.5 shows a multi-table query that includes fields from the Employees, Departments, and Expenses tables.

   Figure 19.5. Multi-table queries enable you to pull data from fields on more than one table.

   

5. When you have finished designing the multi-table query, you can run it. Click the Run button on the toolbar.

The query results appear in the Datasheet view. Combining data from related tables into one query allows you to create other objects from that query, reports .