Access 97/2000: Creating a Report Based on Multiple Queries

In Microsoft Access, you can base a report on two or more tables and queries. For example, a monthly sales report might include data from the Monthly Sales Details query, the Region table, and the Sales Representative table. Before creating a report based on multiple objects, you must ensure that the underlying objects are related. You can either define relationships between them, or make sure that Access can create joins between them. Access can join two objects if each object has a field with the same or compatible data type and if one of the join fields is a primary key.

If you are creating a new report based on multiple objects, you can either use the Report Wizard or create it yourself in Design view. If you want to modify an existing report to include data from multiple objects, you must do it in Design view.

To create a report based on multiple objects by using the Report Wizard

1. In the Database window, click Reports under Objects, and then click New.
2. In the New Report dialog box, click Report Wizard, and then click OK.
3. In the Tables/Queries list, click one of the tables or queries that you want to base your report on.
4. Add the fields that you want to include in the report to the Selected Fields list.
5. Repeat steps 3 and 4 for each table or query that you want to base the report on.
6. Click Next and follow the instructions in the wizard.

To base a report on multiple objects in Design view

1. Open the report in Design view and display its property sheet.
2. On the Data tab, click the Build button (...) next to the RecordSource box.
3. On the Query menu, click Show Table.
4. In the Show Table dialog box, click the tab that lists the type of objects whose data you want to include, and then add the tables and queries you want.
5. Drag the fields that you want to include in the report from the field lists in the top portion of the window to the columns in the query design grid below.
6. Save your changes and close the Query Builder.
7. In report Design view, click Field List on the View menu. If the field list is already displayed, close and reopen it to update its contents.
8. Drag the fields you want from the field list to the report.