Help With Grand Total for Subreports

[ridgerunner]

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]

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.

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)

 

[ridgerunner]

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

 

[Marshall Barton]

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.

 

[ridgerunner]

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.

 

[Evi]

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

 

[ridgerunner]

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

When I click on the properties for the subreport, I do not see "name" under "other". Would that be the same as "caption"?

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.

 

[ridgerunner]

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

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

When I click on the properties for the subreport, I do not see "name" under "other". Would that be the same as "caption"?

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.

 

[Evi]

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

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

ridgerunner wrote:

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.


ridgerunner wrote:
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

When I click on the properties for the subreport, I do not see "name"

under "other". Would that be the same as "caption"?

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.

 

[Marshall Barton]

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.

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.

 

[ridgerunner]

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

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

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

:

ridgerunner wrote:

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.


ridgerunner wrote:
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

When I click on the properties for the subreport, I do not see "name" under "other". Would that be the same as "caption"?

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.

 

[ridgerunner]

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.

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.

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.

 

[Evi]

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

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

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

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

:



:

ridgerunner wrote:

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.


ridgerunner wrote:
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

When I click on the properties for the subreport, I do not see

"name"
under "other". Would that be the same as "caption"?

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.

 

[ridgerunner]

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?

 

[Marshall Barton]

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.

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

 

[Marshall Barton]

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?

No, I do not agree. People do it all the time.

I am having trouble figuring out why you xan not get it to
work, especiall if you can get each subreport total in it's
own main report text box. To get the sum of the three
values, just add them together as I said earlier.

Maybe I don't understand what you are actually doing or
what's wrong with the total you are getting. I might get a
better grip on it if you would post a Copy/Paste of the
grand total text box expression, the value it displays, the
values in each subreport subtotal and the value you expect
to see.

 

[ridgerunner]

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.