DataBase - Help With Grand Total for Subreports

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?

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]

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

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]

"Marshall Barton" wrote:

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

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.

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

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]

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

=[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 £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 :(


Evi


error on
a
in
not
Design
view (I
and
this
the
under "other".  Would that be the same as "caption"?
get:

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

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.

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
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



fine
the
the
of a
have
experienced
have a
am
Design
Tab
changing
report in
I
Reports![SubRptProd_Cust_Clean]![SubTotProd_Cust_Clean]

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

--
Marsh
MVP [MS Access]

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?

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.