Create and use subreports

When you are working with relational data (where related data is stored in separate tables), you often need to view information from more than one table or query on the same report. For example, you want to see customer data, but you also want to see information about the customer's orders at the same time. A subreport is a useful tool for doing this because it allows you to display the order information on the report with the customer information, in a logical and readable way. Microsoft Office Access 2007 provides many ways to help you create subreports quickly.

What do you want to do?

Understand subreports

Create a subreport

Make design changes to a subreport

Understand subreports

A subreport is a report that is inserted in another report. When you combine reports, one of them must serve as the main report that contains the other report. A main report is either bound or unbound. A bound report is one that can display data and has a table, query, or SQL statement specified in its Record Source property. An unbound report is one that is not based on a table, query, or SQL statement (that is, the Record Source property of the report is empty).

Tip: In Access 2010, reports have control layouts, which help keep things lined up and looking good! 

Example of an unbound main report with two unrelated subreports    

An unbound main report cannot display any data of its own, but it can still serve as a main report for unrelated subreports that you want to combine.

1. The unbound main report contains two subreports.

2. One subreport summarizes sales by employee.

3. The other subreport summarizes sales by category.

Example of a main report and subreport that are bound to the same record source    

You can use the main report to show detail records, such as every sale in a year, and then use a subreport to show summary information, such as the total sales for each quarter.

1. The subreport summarizes the year's sales by quarter.

2. The main report lists the day-to-day sales.

Example of a main report and a subreport that are bound to related record sources    

A main report can contain data common to one or more subreports. In this case, the subreport contains data that is related to the data in the main report.

1. The main report lists the name and city of each fair.

2. The subreport lists the representatives who will attend each fair.

Subforms on a report

A main report can include subforms in addition to subreports, and it can include as many subforms and subreports as you want. Additionally, a main report can contain up to seven levels of subforms and subreports. For example, a report can contain a subreport, and that subreport can contain a subform or a subreport, and so on, up to seven levels deep.

If you add a subform to a report and then open the report in Report view, you can use the subform to filter and navigate through records. Visual Basic for Applications (VBA) code and embedded macros that are attached to the form and its controls will still run, although some events are disabled in this context. You cannot add, edit, or delete records by using a subform on a report.

Linking a report to a subform or subreport

When you insert a subform or a subreport that contains information that is related to data in the main report, the subreport control must be linked to the main report. The link ensures that the records displayed in the subform or subreport correspond correctly to the records printed in the main report.

When you create a subform or subreport by using a wizard or by dragging an object from the Navigation Pane to a report, Access automatically links the subform or subreport to the main report if either of the following conditions are met.

• You define relationships for the tables you select, or you define relationships for the tables underlying the queries you select.

  For more information about creating relationships, see the links in the See Also section.

• The main report is based on a table with a primary key, and the subform or subreport is based on a table that contains a field with the same name as that primary key and has the same or a compatible data type. For example, if the primary key of the table underlying the main report is an AutoNumber field, and its FieldSize property is set to Long Integer, the corresponding field in the table underlying the subform or subreport must be a Number field with its FieldSize property set to Long Integer. If you select a query or queries as the record source of a subform or subreport, the underlying tables in the query or queries must meet the same conditions.

Top of Page

Create a subreport

If the subreport will be linked to the main report, ensure that the underlying record sources are related before using the following procedure. For more information about creating relationships, see the links in the See Also section.

Use the Subreport Wizard to create a subreport

1. Open the report that you want to use as the main report in Design view.

2. On the Design tab, in the Controls group, ensure that Use Control Wizards is selected.

3. On the Design tab, in the Controls group, click Subform/Subreport .

4. On the report, click where you want to place the subreport.

5. On the first page of the Subreport Wizard, if you want to create a new subform or subreport and base it on a table or query, click Use existing Tables and Queries. If there is an existing report or form that you want to use as the subreport, click Use an existing report or form, select the report or form in the list, and then click Next.

   

6. If you chose Use an existing report or form on the previous page of the wizard, skip this step and go directly to step 7. Otherwise, from the Tables/Queries list, select the table or query that contains the fields that you want included on the subreport, and then double-click the fields that you want in the Available Fields list to add them to the subreport.

   

   If you plan to link the subreport to the main report, be sure to include the field or fields that will be used to create the link, even if you don't want them to be displayed. Typically, the linking field is an ID field. In the preceding illustration, the Orders table is the record source for the subreport, and the Customers table is the record source for the main report. Because the Orders table is related to the Customers table by the Customer ID field, that field is added to the Selected Fields list.

   Note: You can include fields from multiple tables and queries on your subreport. When you finish adding fields from one table, select the next table or query from the Tables/Queries list and then add the fields that you want.

   Click Next to continue.

7. On this page of the wizard, you determine how to link the subreport to the main report. If Access finds fields that appear to be suitable for linking the subreport to the main report, the wizard displays a list of possible linking suggestions.

   

   You can select the linking suggestion that seems most appropriate for your situation or, if you do not want the subreport to be linked to the main report, select None. If you want to link the subreport to the main report, but none of the suggestions seem appropriate, click Define my own.

   Note: If the wizard cannot find suitable fields for linking, it does not provide a list of linking suggestions and automatically selects the Define my own option.

   

   When the Define my own option is selected, the wizard displays two sets of lists.

   • Under Form/report fields, select the field or fields from the main report that you want to use to link the main report to the subform or subreport. You can select up to three fields, and each field you select must match a related field in the subform's or subreport's data source.

   • Under Subform/subreport fields, select the corresponding field or fields from the subform or subreport that link to the main report fields that you selected.

   • To leave the subform or subreport unlinked to the main report, ensure that all the lists are empty.

     Click Next to continue.

8. On the last page of the wizard, type a name for the subform or subreport, or just click Finish to accept the default.

   

Access adds a subreport control to your report and binds the control (that is, it sets the control's Source Object property) as follows:

• If you selected Use an existing report or form on the first page of the wizard, Access binds the subreport control to the report or form that you specified.

• If you selected Use existing Tables and Queries on the first page of the wizard, Access creates a new report object in the Navigation Pane and then binds the subreport control to it. The name of the new report object is the same as the name you typed on the last page of the wizard.

Add a table, query, form, or report to a report as a subreport

A quick way to add a subreport to a report is to open the main report in Design view and then drag an object from the Navigation Pane to it. If you want the subform or subreport to be linked to the main report, ensure that the underlying record sources are related and that the record sources include the fields that will be used to link the subform or subreport to the main report. For more information about creating relationships, see the links in the See Also section.

1. In the Navigation Pane, right-click the report you want to use as the main report, and then click Design View on the shortcut menu.

2. Drag a table, query, form, or another report from the Navigation Pane to the section of the main report where you want the subreport to appear.

Access does one of the following:

• If Access can determine how to link the two objects, Access adds a subreport control to the report. If you add a form or report, Access binds the subreport control to that object. If you add a table or query, Access first creates a report object and then binds the subreport control to that new object.

• If Access cannot determine how to link the two objects, the Subreport Wizard appears. To continue, follow the procedure in the section Create a subreport, starting with step 7.

  After you complete the wizard, Access adds the subreport to the report.

Use the following procedure to double-check that the subform or subreport is correctly linked to the main report.

1. Click the subreport control to select it.

2. If the property sheet is not already displayed, press F4 to display it.

3. On the Data tab of the property sheet, examine the Link Master Fields and Link Child Fields properties.

   • For an unlinked subform or subreport, both of these properties should be blank.

   • For a linked subform or subreport, the Link Master Fields and Link Child Fields properties should display the field or fields that relate the two objects together. For example, if the main report displays information from the Employees table and the subform or subreport displays information from the Orders table, the Link Master Fields property should display the ID field from the Employees table and the Link Child Fields property should display the Employee ID field from the Orders table.

You might discover that these properties need to be edited for the subform or subreport to work properly. Use the following procedure.

1. On the Data tab of the property sheet, click the Link Master Fields property box, and then click .

   The Subreport Field Linker dialog box appears.

   

2. In the Master Fields and Child Fields lists, select the fields that you want to link the reports with. If you are not sure which fields to use, click Suggest to make Access try to determine the linking fields. When you finish, click OK.

   If you do not see the field that you want to use to link the report, you need to edit the record source of the main report or of the subform or subreport to ensure that it contains the linking field. For example, if the report is based on a query, you should ensure that the linking field is present in the query results.

3. Save the main report and switch to Report view, and then verify that the report works as you expect.

Add a table or query to a report as a datasheet

A datasheet is a simple visual representation of data, similar to a spreadsheet. Each column in a datasheet represents a field in the source table or query, and each row represents a record. You can use the Subform/Subreport control to display a datasheet on a report. This is a good technique to use in situations where you want a compact display of data but do not need the formatting capabilities of a form or report object. To add a datasheet to a report:

1. In the Navigation Pane, right-click the report you want to use as the main report, and then click Design View on the shortcut menu.

2. On the Design tab, in the Controls group, ensure that Use Control Wizards is not selected.

3. On the Design tab, in the Controls group, click the Subform/Subreport tool .

4. On the report, click where you want to place the subreport.

5. If the Subreport Wizard starts, click Cancel to close it.

6. If the property sheet is not already displayed, press F4 to display it.

7. On the report, click the new subreport control to select it.

8. On the Data tab of the property sheet, click the arrow in the Source Object property box, and then click the table or query that you want to display in the subreport control. For example, to display the Orders table, click Table.Orders.

   Access attempts to link the datasheet to the main report, based on the relationships that were defined in the database.

9. On the Data tab of the property sheet, examine the Link Master Fields and Link Child Fields properties.

   • For an unlinked datasheet, ensure that both of these properties are blank.

   • For a linked datasheet, ensure that the Link Master Fields and Link Child Fields properties display the field or fields that relate the main report to the datasheet. For example, if the main report displays information from the Employees table and the datasheet displays information from the Orders table, the Link Master Fields property should display the ID field from the Employees table, and the Link Child Fields property should display the Employee ID field from the Orders table.

In some cases, you might need to set the link field properties yourself. You can do this by using the following procedure.

1. On the Data tab of the property sheet, click the Link Master Fields property box, and then click .

   The Subreport Field Linker dialog box appears.

   

2. In the Master Fields and Child Fields lists, select the fields that you want to use to link the main report to the datasheet. If you are not sure of which fields to use, click Suggest to make Access try to determine the linking fields. When you finish, click OK.

   If you do not see a field that you want to use for linking, you need to edit the record source of the master report or the datasheet to ensure that it contains the linking field. For example, if the datasheet is based on a query, you should ensure that the linking field is present in the query results.

3. Save the main report and switch to Report view, and then verify that the report works as you expect.

Top of Page

Make design changes to a subreport

After you add a subreport to a report, you might want to make design changes to the subreport, or you might want to refer to subreport data on the main report. The following sections provide tips on how to accomplish these tasks.

Open a subform or subreport in a new window in Design view

To make design changes to a subform or subreport while you are working on its main report in Design view, you can open the subform or subreport in its own window.

1. Click the subform or subreport to select it.

2. On the Design tab, in the Tools group, click Subreport in New Window .

Note: This command is not available if the subform/subreport control is bound to a table or query.

Display a total from a subreport on the main report

Suppose you use a subreport named Orders subreport which contains a text box named Shipping Fee Total, and that text box calculates the sum of the Shipping Fee column. To display the sum from the subreport on the main report, you must add a text box to the main report and then use an expression to refer to the Shipping Fee Total text box on the subreport. You can do this by using the following procedure.

1. Right-click the main report in the Navigation Pane, and then click Design view on the shortcut menu.

2. On the Design tab, in the Controls group, click Text Box.

3. On the main report, click where you want to place the new text box.

4. If the property sheet is not already displayed, press F4 to display it.

5. On the Data tab of the property sheet, in the Control Source property box, type the following expression.

   =IIf(IsError([Orders subreport].[Report]![Shipping Fee Total]),0,[Orders subreport].[Report]![Shipping Fee Total])

   Notes: 

   • In this example, you could use the more simple expression =[Orders subreport].[Report]![Shipping Fee Total] but then, if the subreport doesn't contain any data, the control on the main report displays #Error. Using the IsError function within the IIf function, as shown in the first expression, ensures that the text box on the main report displays a zero (0) if the subreport does not return any data.

   • You can use the Expression Builder to create the expression by clicking in the Control Source property box.

6. On the Format tab of the property sheet, set the Format property to the appropriate value (in this case, Currency).

7. Save the report, and then switch to Report view to verify that the calculation works as you expect.

Top of Page