Summarizing results from crosstab queries

[JHSam]

I have a crosstab query that counts the number of flags that are applied to data (e.g., counts the number of A's, B's and C's). When I try to run a simple query on the crosstab query to, say, give me a total number of A's and B's, the query does not seem to recognize the output from the crosstab query as numbers (it yields null fields). Has anyone come across something similar to this and is there a workaround?

Thanks.

 

[JHSam]

I got it figured out. You have to do two things:

First, you need to set the Field Properties|Format in the appropriate Crosstab query field to a numeric format.

Second, in the simple query summary you need to use the Nz() function to probably get the null fields treated as zero values (otherwise the summations don't work correctly).

 

[Dugantrain]

Have you tried the Nz function you just mentioned? I've tried to use this function to accomplish the same task, but even though the zeros were in the right fields, it still treated them as Nulls and therefore wouldn't calculate. If it's working, great, if not, then try the Iif function.

 

[JHSam]

It seemed to work fine, since the calculated summation field values were correct. However, I did notice that it didn't replace the null fields with zeroes in the output of the query fields. You were using it in the format of NZ(<variable>,0) when you were working with this, correct?

Thanks for the tip on the IIF function.