Prev: How do I print multiple copies of the same record on labels?
Next: Mulitple Query Report
From: Darrell on 15 Jul 2008 11:26
I have a main report with 3 subreports and I want to display totals from
each subreport and grand totals in the report header of the main report.
What is the best way to do this?

Allen Browne posted the following response to a user back in 2003
offering a method. But, I can't get the
[rptInvoiceDogs].[Report].[HasData]" (adjusted to my specific
report/control names, of course) to be recognized.

"... No code is needed. At design time, set the Control Source
of the text box to something like this:
=IIf([rptInvoiceDogs].[Report].[HasData],
Nz([rptInvoiceDogs].[Report]![txtSumSalesPrice],0), 0)
+ IIf([rptInvoiceCharges].[Report].[HasData],
Nz([rptInvoiceCharges].[Report]![txtSumTotCharge],0), 0)
+ IIf([InvoiceAdjustments].[Report].[HasData],
Nz([InvoiceAdjustments].[Report]![txtAdjTot],0), 0)"

Any help is greatly appreciated.

Darrell
From: Allen Browne on 16 Jul 2008 03:45
The core ideaa here are:
a) to bring back the totals from the subreports and
b) to accumulate the totals to show at the end of the main report.

For (a), see:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html
It explains the expression you referred to.

Your 3 subreports could be in different sections of the main report? If so,
it' is important to have the subtotal text box on the *same* section as the
subreport itself. Perhaps you could start with 3 text boxes - one showing
the total for each subreport.

Once you are correctly showing the subtotal for each subreport, you can
start working on (b). This requires another text box, with its ControlSource
set to the name or your subtotal text box, and Running Sum set to accumulate
the total. Example:
Control Source =[txtSubTotal]
Running Sum Over All
You should now see the correct subtotal, and the accumulating total
througout the report.

Once you have that working, just place a text box in the Report Footer
section, and sum the values in the 3 accumulated totals. The ControlSource
will be something like this:
=Nz([a],0) + Nz([b],0) + Nz([c],0)
where a, b, and c represent the names of the 3 boxes that accumulate your
subtotals.

Once that's working, you can set the Visible property of the other text
boxes to No so you don't see them if you wish.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Darrell" <dcyphers(a)llu.edu> wrote in message
news:uUvqm7o5IHA.1592(a)TK2MSFTNGP04.phx.gbl...
>I have a main report with 3 subreports and I want to display totals from
>each subreport and grand totals in the report header of the main report.
>What is the best way to do this?
>
> Allen Browne posted the following response to a user back in 2003 offering
> a method. But, I can't get the [rptInvoiceDogs].[Report].[HasData]"
> (adjusted to my specific report/control names, of course) to be
> recognized.
>
> "... No code is needed. At design time, set the Control Source
> of the text box to something like this:
> =IIf([rptInvoiceDogs].[Report].[HasData],
> Nz([rptInvoiceDogs].[Report]![txtSumSalesPrice],0), 0)
> + IIf([rptInvoiceCharges].[Report].[HasData],
> Nz([rptInvoiceCharges].[Report]![txtSumTotCharge],0), 0)
> + IIf([InvoiceAdjustments].[Report].[HasData],
> Nz([InvoiceAdjustments].[Report]![txtAdjTot],0), 0)"
>
> Any help is greatly appreciated.
>
> Darrell

From: Darrell on 16 Jul 2008 11:52
Allen Browne wrote:
> The core ideaa here are:
> a) to bring back the totals from the subreports and
> b) to accumulate the totals to show at the end of the main report.
>
> For (a), see:
> Bring the total from a subreport onto a main report
> at:
> http://allenbrowne.com/casu-18.html
> It explains the expression you referred to.
>
> Your 3 subreports could be in different sections of the main report? If
> so, it' is important to have the subtotal text box on the *same* section
> as the subreport itself. Perhaps you could start with 3 text boxes - one
> showing the total for each subreport.
>
> Once you are correctly showing the subtotal for each subreport, you can
> start working on (b). This requires another text box, with its
> ControlSource set to the name or your subtotal text box, and Running Sum
> set to accumulate the total. Example:
> Control Source =[txtSubTotal]
> Running Sum Over All
> You should now see the correct subtotal, and the accumulating total
> througout the report.
>
> Once you have that working, just place a text box in the Report Footer
> section, and sum the values in the 3 accumulated totals. The
> ControlSource will be something like this:
> =Nz([a],0) + Nz([b],0) + Nz([c],0)
> where a, b, and c represent the names of the 3 boxes that accumulate
> your subtotals.
>
> Once that's working, you can set the Visible property of the other text
> boxes to No so you don't see them if you wish.
>
Allen,

Thank you very much! I am doing this as contract work on the side, so
won't be able to implement this until this evening, but I assure you I
will work on it then, and will post back.

One point for clarification: all 3 of my subreports are in the detail
section of the main report, so... if I understand correctly, I need to
place the subtotal text box(es) for the 3 subreports in the detail
section (of the main report). Correct?

Then the box for the total of all the subreports would go in the main
report header (in this case, rather than footer), with its control
source set to add each of the boxes in the detail section.

Thank you again for your response. It is greatly appreciated.

Darrell
From: Allen Browne on 16 Jul 2008 22:47
Replies in-line

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Darrell" <dcyphers(a)llu.edu> wrote in message
news:%23zN93u15IHA.2240(a)TK2MSFTNGP02.phx.gbl...
> One point for clarification: all 3 of my subreports are in the detail
> section of the main report, so... if I understand correctly, I need to
> place the subtotal text box(es) for the 3 subreports in the detail section
> (of the main report). Correct?

Yes.

> Then the box for the total of all the subreports would go in the main
> report header (in this case, rather than footer), with its control source
> set to add each of the boxes in the detail section.

It will work in the Report Footer. You would need to test in in
Report_Header. There might be a timing issue, where the total is zilch at
the time when the report header is formatted, so you don't get the answer
you need.

Access does do a double-pass, so you may be able to solve that by placing
the total in the report footer in a text box named (say) txtTotal, and then
place a text box in the Report Header that reads the value from the footer,
i.e.:
=[txtTotal]

From: Darrell on 17 Jul 2008 11:23
Allen Browne wrote:
> The core ideaa here are:
> a) to bring back the totals from the subreports and
> b) to accumulate the totals to show at the end of the main report.
>
> For (a), see:
> Bring the total from a subreport onto a main report
> at:
> http://allenbrowne.com/casu-18.html
> It explains the expression you referred to.
>
> Your 3 subreports could be in different sections of the main report? If
> so, it' is important to have the subtotal text box on the *same* section
> as the subreport itself. Perhaps you could start with 3 text boxes - one
> showing the total for each subreport.
>
> Once you are correctly showing the subtotal for each subreport, you can
> start working on (b). This requires another text box, with its
> ControlSource set to the name or your subtotal text box, and Running Sum
> set to accumulate the total. Example:
> Control Source =[txtSubTotal]
> Running Sum Over All
> You should now see the correct subtotal, and the accumulating total
> througout the report.
>
> Once you have that working, just place a text box in the Report Footer
> section, and sum the values in the 3 accumulated totals. The
> ControlSource will be something like this:
> =Nz([a],0) + Nz([b],0) + Nz([c],0)
> where a, b, and c represent the names of the 3 boxes that accumulate
> your subtotals.
>
> Once that's working, you can set the Visible property of the other text
> boxes to No so you don't see them if you wish.
>
Thanks again, Allen. Step 1 is working beautifully. Apparently the Name
AutoCorrect was the problem since I had tried it previously with both
the subreport name and the subreport's control name with the same
result. This time, it worked immediately. I have heard echoes of
problems with this feature before. Your explanation of its origin and
bugs has been extremely helpful.

I've just spent some time on step 2 and am a little confused about what
the Running Sum would be referring to. My efforts have been a bit
cursory so far, so don't spend a lot of time trying to address a vague
confusion. I'll post again with more definitive questions (or a
statement of success) when I've given it fairer attention.

Thank you again for your help.

Darrell
 |  Next  |  Last
Pages: 1 2
Prev: How do I print multiple copies of the same record on labels?
Next: Mulitple Query Report