subtotals in group footers

  • Thread starter Thread starter bob
  • Start date Start date
B

bob

Hi,

Does anyone know of a way to create subtotals in a report that looks
similar to what I have below? I know I can do this using a subreport
but there are too many records and the subreport is slowing down the
main report to the point the report is not functional. There could be
as many as 35-50 different subtotals in my real report. Thanks in
advance for the help!!

Underlying Table Structure:

Field1 Field2
A 5
B 3
A 7
C 1
B 4

Need subtotals in the group footer to look like this:

A 12
B 7
C 1
 
A subreport based on a totals query should not slow down a report too much.
Is Field1 indexed?

What do you mean by 35-50 different subtotals?
 
bob said:
Does anyone know of a way to create subtotals in a report that looks
similar to what I have below? I know I can do this using a subreport
but there are too many records and the subreport is slowing down the
main report to the point the report is not functional. There could be
as many as 35-50 different subtotals in my real report. Thanks in
advance for the help!!

Underlying Table Structure:

Field1 Field2
A 5
B 3
A 7
C 1
B 4

Need subtotals in the group footer to look like this:

A 12
B 7
C 1
Click to expand...

If you group on Field1, then the group header or footer can
calculate and display the group total in a text box by using
the expression =Sum(Field2)
 
Thank you all for the quick responses. Yes Field1 is indexed. The
report is running so slow that I went ahead and put the results of the
totals query into it's own table and then added the index on Field1.

What I meant by 35-50 different subtotals is this.

Group 1234 - Could have subtotals for A,B,C,D,E,F..... out to 35-50
different subtotaled values.

Group 5678 - Could have subtotals for H,I,J,K,L.... out to 35-50
different subtotaled values.

I hope that made sense.

When I try the main report with the sub-report added it is so slow
that I have never even seen it finish running. I usually give up
after 15-20 minutes. As soon as I delete the sub-report from the main
report the main report opens in just a few seconds. When the
sub-report is added I have Field1 from both tables as the link. I'm
running Access 2000 if that makes a difference.

The underlying table in the main report consists of 31 columns and 723
records.

The underlying table for the sub-report consists of 29 columns and 117
records.

Below if a better illustration of what I'm looking for:

Field1 Field2 Field3 Field4 Field5 Field6 Field7 Field8
Field9 etc...
A 5 3 1 2 7 8 2 4
B 3 5 6 1 3 4 9 1
A 7 2 3 7 4 3 2 1
C 1 9 6 4 2 5 9 7
B 4 2 1 3 4 8 4 3

Subtotals Needed

A 12 5 4 9 11 11 4 5
B 7 7 7 4 7 7 13 4
C 1 9 6 4 2 5 9 3

The subtotals could have A,B,C,D,E,F.... out to 35-50 different
options depending on the Group it is subtotaling.
 
If I sum on Field1 in a Group Footer I will only get the subtotal of
Field1 so it would say this.

Field1 = 20 rather than breaking it down by A = 12, B = 7, C = 1.
Right?
 
Eureka!!!! I found my problem. If the subreport spanned multiple
pages that was causing the problem. For the group footer that the
subreport was in I set the Keep Together flag to No and the report ran
fine and opened up in just a few seconds. WOO HOO!!!! Thanks for the
responses everyone!!
 
bob said:
If I sum on Field1 in a Group Footer I will only get the subtotal of
Field1 so it would say this.

Field1 = 20 rather than breaking it down by A = 12, B = 7, C = 1.
Right?
Click to expand...


Right, I didn't realize that you wanted an entire set of
totals. In this case, I think subreports are the way to go.
Try to find out what is causing the report to be so slow and
fix that.
 
Back
Top