Crosstab with 2 Values

[Ray1127]

I have an Access Report based on a Query with the following fields:

Group Text
SubGroup Text
LOB Text
CaseType Text
CountofCaseType Numeric
New Boolean

I need to present the Data in a crosstab format with Group SubGroup and CaseType as the Rows, LOB as the columns with CountofCaseType and New as the values. since there are 2 values to be summed I can't use the crosstab wizard. I have the following formula in a text box in the report

=IIF([LOB]="MA",[countofcasetype],0) The textbox is set as a running sum over the group and is in the CaseType footer. There are 3 LOB columns with 2 sub-columns under each LOB for cases and new cases.

The only thing that comes out on the report is #Error OR 0. I never get the actual count. What am I missing here??

 

[dhookom]

There is an FAQ in the Access Queries and JET SQL forum that describes how to create a crosstab with more than one Value.

Duane
Hook'D on Access
MS Access MVP