How To Create A Subreport In Access

Create and use subreports in Microsoft Access 2007

When you are working with data in multiple database tables you'll most likely need to use information from different tables, queries and reports. This is where the subreports come in handy. It allows you to create links between different objects and the information will be displayed in a logical, readable way.

Note: if the subreport will be linked to the main report, make sure that the underlying record sources are related.

The Subreport Wizard

In order to create a subreport we will be using the Subreport Wizard.

For the purpose of this tutorial we created the report called "client_sales_report"

Fig1. The report on which we'll base the subreport

1. Open the report in Design View

In the Design ribbon, under the Controls group, make sure that Use Control Wizards is selected.

Fig2. The Use Control Wizards button is selected

2. On the Design ribbon, go to Controls group and click Subform/subreport

Fig3. Subform/subreport button

3. On the report click where you want to place the subreport and the following wizard will be displayed.

Fig4. Click to add the subreport

4. If you want to create the subreport on an existing table or query, tick the appropriate button. If you want to base the subreport on an existing report, you can choose that as well (and then choose the report on which to base the subreport).

For the purpose of this tutorial we'll be choosing "Use existing Tables and Queries". Click Next.

Select the table/query and then select the fields that you want to be displayed in the report.

Note: If you plan to link the subreport to the main report, make sure to include the field(s) that will be used to create the link, even if you don't want them to be displayed. Usually that would be the ID field

Fig5. Select the field(s) which you want to be displayed in the subreport

5. Now, Access 2007 will determine how the subreport will be linked to the report.

Note: if you choose to base the subreport on an existing report you'll skip the previous step.

Choose the appropriate link between the report and subreport and then click on Next.

Alternatively you can define your own link (click on "Define my own").

Fig6. Select the link and click Next

6. Type the name for your subreport and click Finish.

Here's our subreport:

Fig7. The subreport showing only the selected fields

Drag and drop

You can also add a table, query or report to a report as a subreport, by opening the report in Design View and then dragging the object from the Navigation Pane to it.

In this case the steps are:

Right click on the report and choose Design View

Fig8. Open the report in Design View
From the Navigation Pane, drag a report, table or query to the existing report and drop it where you want it to appear.
If Microsoft Access 2007 can determine how to link the report to the added object, it will create the link. Otherwise it will open the Subreport Wizard.