DataBase - Help With Grand Total for Subreports
Asked By ridgerunne on 10-Mar-08 02:47 PM
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?
Marshall Barton replied on 10-Mar-08 08:21 PM
The report footer text box can use an expression like:
=report1.Report.total1 + report1.Repor2.total2 +
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)
MVP [MS Access]
ridgerunne replied on 12-Mar-08 10:30 AM
I tried this using the expression builder but I get the #NAME? error on the
report. What could I be doing wrong?
Marshall Barton replied on 12-Mar-08 01:44 PM
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.
MVP [MS Access]
ridgerunne replied on 12-Mar-08 01:48 PM
Evi replied on 12-Mar-08 03:37 PM
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!
ridgerunne replied on 12-Mar-08 06:55 PM
I tried using the expression builder again and these are the results I get:
It seems that this should work.
ridgerunne replied on 13-Mar-08 11:57 AM
Can the footer of the "main" report use the information in the footer of a
Marshall Barton replied on 13-Mar-08 01:05 PM
No, that should not work. As per my earlier example, that
needs to be:
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.
MVP [MS Access]
Evi replied on 13-Mar-08 12:26 PM
you 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
If you wanted to do a sum with it you could
If your subreport wasn't linked to the main report, then this would be fine
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 £27 but the
last invoice only added up to £5 then your Main report's Footer control
would only see that £5
Sometimes there is no avoidng DSum :(
under "other". Would that be the same as "caption"?
ridgerunne replied on 13-Mar-08 02:12 PM
Thanks Evi. I did not know the limitations on summing subreport data in a
main report. How would DSUM be used?
ridgerunne replied on 13-Mar-08 02:18 PM
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.
Evi replied on 13-Mar-08 03:14 PM
If 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
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
then you can put a text box anywhere you want in your main report
and type in it
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
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
Marshall Barton replied on 13-Mar-08 04:18 PM
Go back and reread my first reply in light of your new
MVP [MS Access]
ridgerunne replied on 13-Mar-08 04:08 PM
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?
ridgerunne replied on 14-Mar-08 11:02 PM
I tried to get a post out here earlier today, but there were problems with
the page. I have been able to get a grand total in the footer of the main
page using the totals from the sub reports. I needed to stop trying to use
the sum function and just add them together as you said, after I straightened
out the subreport control names.