databasedev.co.uk - database solutions and downloads for microsoft access

Create Better MS Access Reports
Report Builder for Microsoft Access helps you create dynamic, professional-looking reports fast! The easy-to-understand wizard helps you with complex tasks like calculated fields, adding subreports, customizing styles, as well as grouping and sorting. Download a free trial today!

Access 2007 - Reports:

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"

The report on which we’ll base the subreport
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.

The Use Control Wizards button is selected
Fig2. The Use Control Wizards button is selected

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

Subform/subreport button
Fig3. Subform/subreport button

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

Click to add the subreport
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

Select the field(s) which you want to be displayed in the subreport
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").

Select the link and click Next
Fig6. Select the link and click Next

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

Here’s our subreport:

The subreport showing only the selected fields
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:

  1. Right click on the report and choose Design View

    Open the report in Design View
    Fig8. Open the report in Design View
  2. 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.

Microsoft Office Access 2007 Desktop DatabasesMicrosoft Office Access 2007 Desktop Databases

Microsoft Access is an application used to create small and midsize computer desktop databases for the Microsoft Windows family of operating systems. It can also be used as a database server for a web-based application.

This electronic book (ebook) provides lessons on how to use Microsoft Office Access 2007 to create and manage databases. The lessons follow a step-by-step format with practical examples.

Download the ebook now - Microsoft Office Access 2007 Desktop Databases