Summing failure in CrossTab based report

  • Thread starter Thread starter John Humble
  • Start date Start date
J

John Humble

I'm preparing "MyReport" in Access 2000, to list about a thousand records
sorted in a two-level grouping. There are seven categories in the outer
group and eight categories in the inner group. The report is based on
"MyQuery" and works well.

The reports needs to end with a summary giving an 8 row x 7 column table
indicating the number of records corresponding to each possible
group/sub-group combination. The table must have an 8th column giving row
sums and a ninth row giving column sums. It's generated, including the Row
Sums column but not the Column Sums row, by "MyCrossTabQuery", which is
based on the appropriate fields from "MyQuery". "MySubReport", designed to
be placed in the Report footer of "MyReport", is then based on
"MyCrossTabQuery". This also works well.

However when I try to produce the Column Sums row in "MySubReport", by using
text box controls "MySumData" (control property "=sum([MyData])") in the
subreport footer (just using one column of "MyData" as an example), I get
the "#error" message rather than a numerical result. There are no empty
cells in the column I'm trying to sum. I know that Access can't sum
calculated controls, but in this case the calculations are done in the
CrossTab Query and not in the Report. I've therefore regarded the text box
controls as standard simple text boxes. Is this correct? Can someone
suggest what I'm doing wrong or how to get around the problem? Or am I on
the wrong tack entirely?

TIA

John
 
Assuming the subreport is based on a crosstab query and one of the columns
generated in the crosstab is [MyData], you should be able to add a text box
with the name txtSumMyData and a control source of
=Sum([MyData])
If this doesn't work, you may need to change your crosstab sql to make sure
you are passing numeric values. When you view your crosstab in datasheet
view, are the columns left or right aligned?
 
Thanks Duane. Based on your response I looked again, more carefully. I'd
done evrything right *except* that I'd accidentally put the sum I was trying
to compute in the Page Footer instead of the Report footer. Putting it in
the right place fixed the problem.

regards

John
 
Back
Top