Totals and gran totals for subreports

[Robert Raley]

I have designed a report that has 3 subreports in it. The grouping is on
say name. Each subreport has a relationship to name.

Depending on the NAME one or two of the subreports may have a null or zero
value.

I would like to have a total of all 3 of the subreports in the NAME section
footer. I would also like to have a grand total at the end of the report.

All I get is error messages.

What can I do correctly and what am I doing wrong.

Thanks

Bob

 

[Larry Linson]

I have designed a report that has 3
subreports in it. The grouping is on
say name. Each subreport has a
relationship to name. Depending on
the NAME one or two of the sub-
reports may have a null or zero
value.

I would like to have a total of all 3 of
the subreports in the NAME section
footer. I would also like to have a
grand total at the end of the report.

All I get is error messages.

What can I do correctly and what
am I doing wrong.

(1) Is this a total of a single quantity, or of a calculation, or ??? That
is, what is the data that is involved?

(2) How are you attempting to get the totals?

(3) What, exactly, is the error message you get?

Two things that might be of interest:

It appears from your question that you nave used "Name" as either a Field or
a Control name. That is not a good idea, because "Name" is an Access
reserved word... it is a property of many objects, and, thus, can be
confusing. Change it to some other descriptive name on the Field, and be
sure the Field and the Control in which it is displayed do not have
identical names because that, too, can be confusing.

There is a functon, NZ (for "Null-to-Zero"), which might be needed if there
is no data for one of the Reports embedded in a Subreport Control. But,
without some details, it is unlikely that anyone here can offer specific
useful suggestions.

Larry Linson
Microsoft Access MVP

 

[Robert Raley]

Larry Please see below

(1) Is this a total of a single quantity, or of a calculation, or ??? That
is, what is the data that is involved?

This is a total of both Some of the sub reports have a total which I am try
to sum. I have used NZ but the fact is if there is no record to display
there is no zero

(2) How are you attempting to get the totals?

I put a text box in the page footer section and did a calcution something
like this = subrep1.tot_min + subrep2.tot_add_ded + and etc.

(3) What, exactly, is the error message you get?

In the text box where I am doing the totals is says #error

Two things that might be of interest:

It appears from your question that you nave used "Name" as either a Field

or

Actually the name feild is Stage_name. I do have one name for a feild that
is called name but it just a simple feild use to story quess what, the name

a Control name. That is not a good idea, because "Name" is an Access
reserved word... it is a property of many objects, and, thus, can be
confusing. Change it to some other descriptive name on the Field, and be
sure the Field and the Control in which it is displayed do not have
identical names because that, too, can be confusing.

There is a functon, NZ (for "Null-to-Zero"), which might be needed if there
is no data for one of the Reports embedded in a Subreport Control. But,
without some details, it is unlikely that anyone here can offer specific
useful suggestions.

See above about using NZ.

Thanks Larry.

Bob

 

[Larry Linson]

This is a total of both Some of the sub reports
have a total which I am try to sum. I have
used NZ but the fact is if there is no record
to display there is no zero

The Calculated Control without information should be Null. The Null should
be replaced by a zero by the NZ function. But, I suspect, in your case, the
Report and Subreport Control may not exist and therefore the control itself
may not exist... not "null" (indeterminate) but "nonexistent", and that's
what is causing the problem.

I put a text box in the page footer section
and did a calcution something like this
= subrep1.tot_min + subrep2.tot_add_ded + and etc.

The Page Footer is not a logical place from which toexpect to access totals
in the footer of SubReports, of which there could (even if not in _your_
Report) be many copies on that page. It doesn't total the totals of all the
Report Footers of the Reports imbedded in all the Subreports.

A similar calculation as the Control Source in Section where the Subreport
Controls actually appear should work... it, however, will itself be a
Calculated Field and, for that reason, you could not Sum it in the Page
Footer.

In the text box where I am doing the totals is says #error

I can understand, from your other explanations. That is just an indication
that Access cannot perform the calculation, or cannot find the value,
specified as Control Source.

Actually the name feild is Stage_name. I
do have one name for a feild that is called
name but it just a simple feild use to story
quess what, the name

Avoiding reserved words as Field names is a good habit to develop. They do
not always cause a problem, but when they do, it can be difficult to
diagnose.

The easiest solution is to display the sum of the totals in a context where
Access can calculate it. Otherwise, you may have to calculate the number you
want, using VBA to save the sum in a variable, in an event of the Section
where it is available. You will need to take care not to calculate it more
than once, because those events can fire more than once per record,
particularly if you are paging back and forth in Preview.

Larry Linson
Microsoft Access MVP