Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Access Reports multible subreports 1

Status
Not open for further replies.

Duncanmcl

Programmer
Dec 23, 2000
61
US
I have a report with one to many subreports. The number can vary. How do I refer to each subreport (or all) when trying to sum a field in each subreport producting a report total.
I have tried to sum from the page break section...but understand Access won't allow and an error is produced.

I have created a text field on the main report and used the sum function, it works but only sums the first occurance.

Thanks in advance Jim
 
I'm not exactly sure what you have or need. You should be able to add values together with a control source like:
=sbrptOne.Report.txtTotOne + sbrptTwo.Report.txtTotTwo...
If any subreport might not return records then you need to use expressions like:
=IIf(sbrptOne.Report.HasData,sbrptOne.Report.txtTotOne,0) + IIf(sbrptTwo.Report.HasData,sbrptTwo.Report.txtTotTwo,0) ...


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
Thks...but each subreport has the same name and fields....its a 2nd occurance of subreport1, different values...but still related to the main report. In my situation I'm listing the breakout of General Ledger accounts for a given payment. So the whole payment is to XYZ on the main report, that amount is brokenout into companies (within a corporate group) and each company (one to many) are displayed in each subreport. Each subreport has different values but has the same conrrol names. I'm trying to track the totals of all sub accounts to the total of the report, and its necessary to refer to each subreport in a sum type control. Thus far, I'm able to display the first subreports total on the main report for each XYZ.But sum(reports!main.subrpt!report.field) does not work?

Jim
 
Normally the totals from subreports can be calculated in totals queries that group by the field used to link master/child. You can then add the totals query(s) to the main report's record source so the totals become available in the main report.

Otherwise, you might be able to set Running Sums on your text boxes on the main report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
The running sum is the answer, thanks. By creating on the subreport/form a running sum field based on each detail line in the subreport, the next subreport includes it at each interation. Then the main report footer group line can contain a text field pointing to the running sum field. Results in a report total by the grouping level data represented by one or more subreports.

Thanks again...DHookom
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top