Others

How to Sum Values from an Access Subreport in the Main Report

Huda Qurayshi Sep 30, 2026 869 views

Question details

The user needs to calculate a grand total in a main MS Access report based on totals calculated within a subreport's footer.

How to Sum Values from an Access Subreport in the Main Report

Product: Microsoft Access
Device & OS: not provided
Scenario: Creating comprehensive database reports where a main report must aggregate total values from its nested subreports.
Observed behavior: The subreport successfully calculates the quantity total in its footer, but the main report lacks a way to display the grand total of these aggregated subreport values.

Before you start

Ensure your MS Access subreport is properly linked to the main report and correctly calculates the initial totals in its own report footer before attempting to pull those values into the main report.

Solution 1Recommended

Use a Hidden Text Box with Running Sum in the Main Report

This method pulls the subreport total into the main report's detail section using a hidden text box, then calculates a running sum to display the grand total in the report footer.

Microsoft Access cannot directly evaluate a sum of a subreport control inside the main report's footer. To bypass this limitation, you must use an intermediate running sum text box in the detail section to accumulate the values first.

Add a hidden text box to the Detail Section

Open the main report in Design View. In the Detail section, add a new text box and name it 'txtTotal'. Open its Property Sheet and set the 'Visible' property to 'No' so it remains hidden during printing.

Link to the subreport total

Set the Control Source of 'txtTotal' to reference the subreport's total field using the syntax: =YourSubReportName!SubReportFooterQntySumTotal. Replace the names with your actual subreport and control names.

Enable the running sum property

Still in the Property Sheet for 'txtTotal', locate the 'Running Sum' property under the Data tab and set it to 'Over All'. This tells Access to accumulate the totals across all records being processed.

Display the grand total in the footer

In the main report footer, add a new text box for the grand total. Set its Control Source to =txtTotal to display the final accumulated value from the detail section.

Naming Accuracy: Ensure you are using the actual Name property of the subreport control on the main report, which may sometimes differ from the name of the subreport itself.

Free Microsoft Office alternative

Looking for a Free and Lightweight Office Suite for Your Data Reports?

While WPS Office does not include a database management tool like MS Access, it provides a comprehensive, free, and highly compatible alternative for Microsoft Word, Excel, and PowerPoint. If you are handling data exports or generating reports, WPS Spreadsheet is an excellent tool for data analysis and visualization.

Highly compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Perfect for formatting and analyzing data exported from database queries.Lightweight and runs smoothly on Windows, Mac, Linux, iOS, and Android.Familiar, intuitive user interface that makes transitioning seamless.Includes built-in PDF editing tools for secure report sharing.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the main report text box show an error instead of the subreport total?

This typically occurs if the subreport name or control name in the reference expression is incorrect. Double-check that =YourSubReportName!ControlName matches the exact Name properties used in your Access design, not just the attached label captions.

Can I sum the subreport values directly in the main report footer using the Sum() function?

No, Microsoft Access does not support using aggregate functions like Sum() on controls that reference a subreport directly in the main report footer. You must use the intermediate running sum technique.

How do I handle #Error values when a subreport has no data?

If a subreport might return no records, the direct reference can result in an #Error, which breaks the running sum. You can wrap the subreport reference in the IIf and IsError functions, or use the HasData property to ensure a zero is returned instead of an error when the subreport is empty.