ACC: How to Display and Total Subtotals from Subreports

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article demonstrates how you can use a total from a subreport on a main report and how you can display a total for all the subreports.

MORE INFORMATION

If you use an expression similar to

   =Sum([MySubReport].Report![Product Sales])

CAUTION: Following the steps in the examples below will modify the sample database Northwind.mdb (or the NWIND.MDB in version 2.0). You may want to back up the Northwind.mdb or NWIND.MDB file or perform these steps on a copy of these databases.

Displaying the Total from a Subreport on a Main Report

To display the total from a subreport on a main report, follow these steps.

1. Open the sample database Northwind.mdb, and open the Sales By Category

       Subreport report in Design view.
   

2. Add a report footer section by clicking Report Header/Footer on the View menu.

   NOTE: In version 2.0, click Report Header/Footer on the Format menu.

3. Set the report header section's Height property to 0.

4. Add a text box to the report footer section and set the following properties:

         Name: Total
         ControlSource: =Sum([ProductSales])
         Format: Currency
   

         NOTE: In version 2.0, there is a space in Product Sales.
   

5. Set the report footer section's Visible property to No.

6. Save and then close the report.

7. Open the Sales By Category report in Design view.

8. Add a text box to the Category Name header section below the subreport and set the following properties for the text box:

         Name: Category Total
         ControlSource: =[Sales by Category Subreport].[Report]![Total]
         Format: Currency
   

9. Preview the Sales By Category report. Note that the total product sales for each category are displayed below the listing of sales per product.

Displaying a Total for All Subreports

To display a grand total for all subreports, you can use either a second subreport or a control's RunningSum property.

Using a Second Subreport:

You can use a second subreport to display a grand total. When you add a second subreport to a group footer section, make sure to link the subreport to the grouped field so that the subreport displays the total for that group. If you place the subreport in the report footer section, do not link the subreport to the grouped field so that the subreport displays a total for all the records.

The following example demonstrates how to use a second subreport to display a grand total on a main report:

1. Open the sample database Northwind.mdb.

2. Open the Sales By Category Subreport report in Design view.

3. Create a copy of the Sales By Category Subreport report clicking Save As/Export on the File menu. Save the report as Sales By Category Subreport Summary.

   NOTE: In version 2.0, click Save As on the File menu.

4. Set the detail section's Visible property to No.

5. Set the report footer section's Visible property to Yes.

6. Save and then close the Sales By Category Subreport Summary report.

7. Open the Sales By Category report in Design view.

8. Add a Subform/Subreport control to the report footer section and set the following properties for the control:

         Name: Sales by Category Subreport Summary
         SourceObject: Report.Sales by Category Subreport Summary
   

9. Preview the report. Note that the total sales per category and a grand total appear on the last page.

To use the RunningSum property to display a grand total on a main report, follow these steps:

1. Open the sample database Northwind.mdb.

2. Open the Sales By Category report in Design view.

3. Add a text box that references the text box you created in step 9 of the "Displaying the Total from a Subreport on a Main Report" section above. Set the following properties for the text box:

         Name: Sales RunningSum
         ControlSource: =Val([Category Total])
         RunningSum: Over All
         Visible: No
   

   NOTE: You use the Val() function to convert the Variant returned by the referenced control to a number so that the RunningSum property accumulates the total.

4. Add a text box control to the report footer section to display the grand total. Set the following properties for the control:

         Name: Grand Total
         ControlSource: =[Sales RunningSum]
   

REFERENCES

For more information about creating totals in reports, search the Help Index for "totals," or ask the Microsoft Access 97 Office Assistant.

For more information about the RunningSum property in Microsoft Access 2.0, search for "RunningSum," and then "RunningSum Property" using the Microsoft Access Help menu. Or see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q115877
   TITLE     : ACC2: RunningSum Text Box in Report Not Accumulating Values

   ARTICLE-ID: Q129096
   TITLE     : ACC: How to Sum a Calculation in a Report