Reports Based on Crosstab Query

[DennisB]

Hi

I have a report based on a crosstab query and need to calculate what % a sub
set of the columns is of the total in each detail row.

I have tried creating a text box where the data is Sum([1]+[2]+[3]
etc)/Total but the text box returns a blank.

Any suggestions would be appreciated.

Dennis

 

[Allen Browne]

If any one of the fields is Null, the result will be null.
Use Nz() to convert *each* field to zero if it is null:
=Sum(Nz([1],0) + Nz([2],0) + Nz([3],0))

BTW, there's a more elegant way to get the row total. Whatever field gives
you the Value for the crosstab, just Sum the field and specify it as another
Row Heading, Example in:
Crosstab query techniques: Display row totals
at:
http://allenbrowne.com/ser-67.html#RowTotal

You could even use Avg instead of Sum, which would solve you 2 more issues:
- the fact that the zeros slew the total incorrectly when there are nulls;
- the division by zero problem.

 

[DennisB]

Thanks very much.