reports >> Help With Grand Total for Subreports

by cmlkZ2VydW5uZXI » Tue, 11 Mar 2008 02:47:02 GMT

I have three subreports, with totals, that I have pulled into one main
report. The totals for the three subreports are working fine, but I can't
get them to sum to a grand total in the report footer. Can someone please
tell me how to do this?

reports >> Help With Grand Total for Subreports

by Marshall Barton » Tue, 11 Mar 2008 08:21:48 GMT



The report footer text box can use an expression like:

=report1.Report.total1 + report1.Repor2.total2 +
report3.Report.total3

But, if there is any chance that a subreport might not have
any data, you need to use:

=IIf(report1.Report.HasData, report1.Report.total1, 0) +
IIf(report2.Report.HasData, report2.Report.total2, 0) +
IIf(report3.Report.HasData, report3.Report.total3, 0)

--
Marsh
MVP [MS Access]

reports >> Help With Grand Total for Subreports

by cmlkZ2VydW5uZXI » Wed, 12 Mar 2008 22:30:01 GMT

I tried this using the expression builder but I get the #NAME? error on the
report. What could I be doing wrong?

reports >> Help With Grand Total for Subreports

by Marshall Barton » Thu, 13 Mar 2008 01:44:57 GMT


What did you use as the subreport control names? Note the
the subreport **control** names can be different from the
name of the report object it will display.

--
Marsh
MVP [MS Access]

reports >> Help With Grand Total for Subreports

by cmlkZ2VydW5uZXI » Thu, 13 Mar 2008 01:48:00 GMT



reports >> Help With Grand Total for Subreports

by Evi » Thu, 13 Mar 2008 03:37:05 GMT


the



I tried to use the name of the subreports and the field I need to have a
grand total for at the end of the report. I am not very experienced in
doing this sort of thing with Access. I have 3 subreports that have a
subtotal that I need to sum together at the end of the report. I am not
certain that I am interpreting the word 'control' correctly.

A Control is anything that is in the report when you look at it in Design
View. this includes the fields, the text boxes you add, the lines,
subreports, pictures, labels etc.

To find the name of your subreport, open the *main* report in Design view (I
find them quite tricky to select in Acc2k)
Click on the Subreport. In the Properties box, click on the Other Tab and
look next to Name. I usually save myself alot of confusion by changing this
name if I have to, so that it is the same as the name of the report in the
Database window - it isn't always!
Evi

reports >> Help With Grand Total for Subreports

by cmlkZ2VydW5uZXI » Thu, 13 Mar 2008 06:55:01 GMT


I tried using the expression builder again and these are the results I get:

=Sum([Score])+ Reports![SubRptProd_Cust_Clean]![SubTotProd_Cust_Clean]

It seems that this should work.

reports >> Help With Grand Total for Subreports

by cmlkZ2VydW5uZXI » Thu, 13 Mar 2008 23:57:00 GMT

Can the footer of the "main" report use the information in the footer of a
subreport?

reports >> Help With Grand Total for Subreports

by Evi » Fri, 14 Mar 2008 00:26:25 GMT

ou can if you put the word Report between the subreport name and the
'control' name (find the name of the control, and change it if you wish,
using the Property box.

So if the text box in your subreport is called MyText
and your subreport *Control* is called MySub
Then in your main report you can have a text box which says

=[MySub].[Report].[MyText]

If you wanted to do a sum with it you could

=Sum([MyCosts])-[MySub].[Report].[MyText]
If your subreport wasn't linked to the main report, then this would be fine

BUT

with a linked subreport the main form's control won't be adding up all the
items in your Subreport. It will only show you want is in the last
*filtered* view of the subreport.

So if you were adding up invoices and the invoices added up to ?7 but the
last invoice only added up to ? then your Main report's Footer control
would only see that ?

Sometimes there is no avoidng DSum :(


Evi


"ridgerunner" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
error on
a
in
not
Design
view (I
and
this
the
under "other". Would that be the same as "caption"?
get:



reports >> Help With Grand Total for Subreports

by Marshall Barton » Fri, 14 Mar 2008 01:05:10 GMT


No, that should not work. As per my earlier example, that
needs to be:

[SubRptProd_Cust_Clean].REPORT![SubTotProd_Cust_Clean]

but as Evi tried to explain, it doesn't matter what the name
of the (sub)report is. The name of the subreport
***Control*** on the main report must be
SubRptProd_Cust_Clean for that to work.

--
Marsh
MVP [MS Access]

reports >> Help With Grand Total for Subreports

by cmlkZ2VydW5uZXI » Fri, 14 Mar 2008 02:12:00 GMT

hanks Evi. I did not know the limitations on summing subreport data in a
main report. How would DSUM be used?

"Evi" wrote:


reports >> Help With Grand Total for Subreports

by cmlkZ2VydW5uZXI » Fri, 14 Mar 2008 02:18:05 GMT

I am sorry Marshall. I did not realize the significance of the word Report
in between the report name and the field. I did find the subreports' control
names on the main report and fixed them. I can now get the subtotals to
print in the footer of the main report but they will not sum, as Evi stated
would not happen.

reports >> Help With Grand Total for Subreports

by Evi » Fri, 14 Mar 2008 03:14:30 GMT

f I'm teaching my grandmother to suck eggs please forgive me.

DSum goes back to the query on which your subreport is based and adds up
what you want ,using whatever criteria you want.
It's easy to do with a number field and a bit trickier to do with date and
text fields.
So say your subreport was based on a query called QrySums
and the field you wanted to add up in your subreport footer was called
MyAmount

then you can put a text box anywhere you want in your main report

and type in it

=DSum("[MyAmount]","QrySums")

You can add conditions to this so if you only wanted to add MyAmount if the
CustomerNumber field in QrySums had a 5 in it, you could write

=DSum("[MyAmount]","QrySums","[CustomerNumber]=5")

You can also have the DSum refer to a control in your main report

So if you have a Text box (txtTotal) with a calculation in it you could say

=DSum("[MyAmount]","QrySums","[CustomerNumber]=" & [txtTotal])

The placing of the quote marks is really important. They would also be
different if CustomerNumber was a text field or a date field.

You could add stuff from your current report to DSums so if your main report
has a field called MainAmount your text box could say

=Sum([MainAmount])-DSum("[MyAmount]","QrySums")

Evi



"ridgerunner" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
fine
the
the
of a
#NAME?
have
experienced
have a
am
Design
Tab
changing
report in
"name"
I
Reports![SubRptProd_Cust_Clean]![SubTotProd_Cust_Clean]



reports >> Help With Grand Total for Subreports

by cmlkZ2VydW5uZXI » Fri, 14 Mar 2008 04:08:03 GMT

I did go back over your first reply after reading Evi's reply and that is
when things became clearer. The main report will not create a grand total in
the footer. I can now make the individual subtotals print in the footer but
not a grand total. Do you agree that the main report will not calculate a
grand total from the sub reports totals?

reports >> Help With Grand Total for Subreports

by Marshall Barton » Fri, 14 Mar 2008 04:18:36 GMT


Go back and reread my first reply in light of your new
undestanding.

--
Marsh
MVP [MS Access]

Similar Threads

1. Adding Subreport totals to Main Report to get Grand Total

2. Grand total from subreport totals

G'day. I've seen other threads along the lines of this question and adapted 
their answers with no success, so I'm going to bother you all with my own 
dilemma. I'm new to subreporting and report totals and have managed to add a 
total to each individual report (there are 3) using the ControlSource field. 
Now I need a grand total for all the subtotals in the report. I have tried 
using the labels for these (Total) like so: 

=((TOTAL_BED_COST!Total)+
(PATIENT_TREATMENTS!Total)+
(PATIENT_MEDICAL_ITEMS!Total))

But this results in 0

So am I on thr right track? All I need is to be able to get a grand total 
from all the subreport totals. Thanks for any help you can give!

3. Grand Total on Main Report from 7 subreports

4. Grand totals in Subreport in Report Footer

In the Group header I have a subreport where the Source Object is a parameter 
query whose criteria is based on a field in the Group Header. The report is 
grouped on the [ProgramArea] field.

The criteria is:
[Reports]![Programs Activities Cumulative]![ProgramArea_Box]

In the Report Footer I want to generate a subreport where ALL the data is 
summed for all groups. I need to use a sub report because the data is from a 
related table with a one to many relationship to the primary table.

How do I set the criteria, or use another method to have the Report Footer 
subreport generate a cumulative for ALL the related table data. I'm hoping to 
use the SAME query but add an OR or AND critieria.

5. Hide SubReports with no data BUT still include them in Grand Total calculation

6. Grand Total that included subreports

I have a report with 4 subreports. I have them all listed within the detail 
section of the main report. In the Report footer section of the main report I 
would like to add a text box that calculates a "Grand Total" of the the main 
report with the 4 subreports.  Each report has a text box that calculates 
"=Sum([Lost Savings]). The name "Total Lost Savings" is the name for each 
text box of each report. All reports are based on a table called "Travelocity 
Air Travel Data". PLEASE HELP ME!

7. subtotal, running total, grand total - Access Forms

8. Sub totals and grand totals on a form

I am trying to make a form display the results of input for 3 separate 
periods of time for different locations. Here is what the form/fields 
represent:
Location: in this field are the names of different offices.
Number of Surveys: In this field a number is entered representing the 
quantity of surveys performed at each location for the week.  From here, I 
need to calculate the number of surveys taken within the quarter, then the 
number of surveys taken during the year.  From there, I need to find the 
cumulative or grand total of all  weekly, quarterly and yearly sums for all 
of the offices.  I tried using a  formula in a query, but I cannot get the 
query formula(or name) to work as the control source on the form.  

Thank you for your help.

Gary