ACC2000: How to Display and Total Subtotals from Subreports

This article was previously published under Q208835
This article has been archived. It is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SUMMARY
This article demonstrates how you can use a total from a subreporton 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])
on a main report to sum a reference to a subreport control that contains asubtotal, you receive a
#Name?
error message. This error message occurs because the Sum() function (and the other aggregate functions) can reference only fields, not controls. To avoid this error, place the sum expression in the subreport's report footer, and then reference that expression in the main report.

Displaying the Total from a Subreport on a Main Report

CAUTION: If you follow the steps in this example, you modify the sample Access project NorthwindCS.adp. You may want to back up the NorthwindCS.adp file and follow these steps on a copy of the project.


To display the total from a subreport on a main report, follow these steps.
  1. Open the sample database Northwind.mdb or the sample project NorthwindCS.adp, and then 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.
  3. Set the Report Header section's Height property to 0.
  4. Add a text box to the Report Footer section, and then set the following properties:
    Name: Total
    Control Source: =Sum([ProductSales])
    Format: Currency
  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 then set the following properties for the text box:
    Name: Category Total
    Control Source: =[Sales by Category Subreport].[Report]![Total]
    Format: Currency
  9. Preview the Sales By Category report. Note that the total of product sales for each category is 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 secondsubreport or a control's RunningSum property.

Using a Second Subreport

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

The following example demonstrates how to use a second subreport to displaya grand total on a main report:
  1. Open the sample database Northwind.mdb or the sample project NorthwindCS.adp.
  2. Open the Sales By Category Subreport report in Design view.
  3. Create a copy of the Sales By Category Subreport report by clicking Save As on the File menu. Save the report as Sales By Category Subreport Summary.
  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 then set the following properties for the control:
    Name: Sales by Category Subreport Summary
    Source Object: Report.Sales by Category Subreport Summary
  9. Preview the report. Note that a grand total appears on the last page.

Using the RunningSum property:

To use the RunningSum property to display a grand total on a main report, follow these steps:
  1. Open the sample database Northwind.mdb or the sample project NorthwindCS.adp.
  2. Open the Sales By Category report in Design view.
  3. Add a text box that references the text box you created in step 8 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
    Control Source: =Val([Category Total])
    Running Sum: 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
    Control Source: =[Sales RunningSum]
    Format: Currency
REFERENCES
For more information about the RunningSum property, click Microsoft Access Help on the Help menu, type runningsum property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.


For additional information on calculating a running sum, click the article number below to view the article in the Microsoft Knowledge Base:
208850 ACC2000: How to Sum a Calculation in a Report
running
Properties

Article ID: 208835 - Last Review: 12/05/2015 10:57:03 - Revision: 2.0

Microsoft Access 2000 Standard Edition

  • kbnosurvey kbarchive kbhowto kbusage KB208835
Feedback