Totals are doubled when report is a sub report

Hi, I hope someone can help before what remaining hair I have is torn out!

I have a report which is a list of names from a query and then for each day
of the week it shows the time that person is due to be in and leave. That is
a simplifies view but pretty much it. In the format section of that report I
keep a count of the number of people in for each day and each possible start
and end time, All this is good and works.

However when I add this report to another as a sub report, the totals I'm
keeping are doubled. On c hecking I found that the subreports Format section
was being invoked (no of people X 2 times). It goes through the list from
start to end and then again. The peoples names aren't being printed twice but
because of the logic being invoked the totals are accumulated twice.

The main report is essentially the same as the sub report except on a
different group/type of people. There is no relevant parent/child field to
link on. The sub-report is in the Footer section of the main report.

Can someone PLEASE shed some light on this, I'm going nuts! If more info is
needed I will gladly provide it.

tia h

Hector wrote:
I have a report which is a list of names from a query and then for each day
of the week it shows the time that person is due to be in and leave. That is
a simplifies view but pretty much it. In the format section of that report I
keep a count of the number of people in for each day and each possible start
and end time, All this is good and works.

However when I add this report to another as a sub report, the totals I'm
keeping are doubled. On c hecking I found that the subreports Format section
was being invoked (no of people X 2 times). It goes through the list from
start to end and then again. The peoples names aren't being printed twice but
because of the logic being invoked the totals are accumulated twice.

The main report is essentially the same as the sub report except on a
different group/type of people. There is no relevant parent/child field to
link on. The sub-report is in the Footer section of the main report.


If you are using code in a Format event to calculate a
total, then that approach is doomed. Report events are
executed in whatever oprder and as many times as beeded to
produce the report. Previewing the report can cause the
calculations to be done again and again generating what may
apprear to be a random value as the total.

Instead of using code in an event procedure, use the
aggregate functions (Count, Sum, etc) and/or text boxes with
their RunningSum property.

--
Marsh
MVP [MS Access]

"Marshall Barton" wrote:

Hector wrote:
I have a report which is a list of names from a query and then for each day
of the week it shows the time that person is due to be in and leave. That is
a simplifies view but pretty much it. In the format section of that report I
keep a count of the number of people in for each day and each possible start
and end time, All this is good and works.

However when I add this report to another as a sub report, the totals I'm
keeping are doubled. On c hecking I found that the subreports Format section
was being invoked (no of people X 2 times). It goes through the list from
start to end and then again. The peoples names aren't being printed twice but
because of the logic being invoked the totals are accumulated twice.

The main report is essentially the same as the sub report except on a
different group/type of people. There is no relevant parent/child field to
link on. The sub-report is in the Footer section of the main report.


If you are using code in a Format event to calculate a
total, then that approach is doomed. Report events are
executed in whatever oprder and as many times as beeded to
produce the report. Previewing the report can cause the
calculations to be done again and again generating what may
apprear to be a random value as the total.

Instead of using code in an event procedure, use the
aggregate functions (Count, Sum, etc) and/or text boxes with
their RunningSum property.

--
Marsh
MVP [MS Access]
.

Thanks for the info Marshall, much appreciated. I'm not sure though how I'm
gonna apply that to my report, as it isn't that straightforward.

The info is shown in a kind of graphical manner. In the detail section I
have a grey rectangle and above it (in the z order) there are 20 white
rectangles (4 for each possible start/end time per working day with their
visib;e property set to false).

In the detail format I check the person's start/end time and make the
relevant rectangles visible (and those in between) and add the relevant
count(s), the total of which is shown in the report footer.



Could I -

Replaces those rectangles with text boxes
Give them a value of 1 for the start and end time and a value of 0 for the
times they are not in or inbetween the start and end time
Sum on the value of those text boxes.

I've tried to do that already but no success, all I get is 0 in my total so
I guess I'm missing something (apart from a brain cell!)

Thanks again for your response.

h.

Hector wrote:
"Marshall Barton" wrote:
Hector wrote:
I have a report which is a list of names from a query and then for each day
of the week it shows the time that person is due to be in and leave. That is
a simplifies view but pretty much it. In the format section of that report I
keep a count of the number of people in for each day and each possible start
and end time, All this is good and works.

However when I add this report to another as a sub report, the totals I'm
keeping are doubled. On c hecking I found that the subreports Format section
was being invoked (no of people X 2 times). It goes through the list from
start to end and then again. The peoples names aren't being printed twice but
because of the logic being invoked the totals are accumulated twice.

The main report is essentially the same as the sub report except on a
different group/type of people. There is no relevant parent/child field to
link on. The sub-report is in the Footer section of the main report.


If you are using code in a Format event to calculate a
total, then that approach is doomed. Report events are
executed in whatever oprder and as many times as beeded to
produce the report. Previewing the report can cause the
calculations to be done again and again generating what may
apprear to be a random value as the total.

Instead of using code in an event procedure, use the
aggregate functions (Count, Sum, etc) and/or text boxes with
their RunningSum property.


Thanks for the info Marshall, much appreciated. I'm not sure though how I'm
gonna apply that to my report, as it isn't that straightforward.

The info is shown in a kind of graphical manner. In the detail section I
have a grey rectangle and above it (in the z order) there are 20 white
rectangles (4 for each possible start/end time per working day with their
visib;e property set to false).

In the detail format I check the person's start/end time and make the
relevant rectangles visible (and those in between) and add the relevant
count(s), the total of which is shown in the report footer.

Could I -

Replaces those rectangles with text boxes
Give them a value of 1 for the start and end time and a value of 0 for the
times they are not in or inbetween the start and end time
Sum on the value of those text boxes.

I've tried to do that already but no success, all I get is 0 in my total so
I guess I'm missing something (apart from a brain cell!)


Using text boxes with 0/1 makes no sense to me so either
that idea is just thrashing around or I need more
explanation.

What relevance does a count of something in the report
footer have with the problem with using event code to
calculate a total? If the count in the report footer us
calculated by using an aggregate functionm then the same
value can be calculated in an invisible report header text
box using the same expression.

--
Marsh
MVP [MS Access]