Using Count within a Cross Tab Query

[billelev]

I have a cross tab query with Date as a row heading and a series of Names as column headings. The Value for each Date/Name intersection can either be -1, 0 or 1.

For example:

Date, Name1, Name2, Name3, Name4
1/1/2000, -1, -1, 0, 1
1/2/2000, 1, 1, 0, 0

I would like to have a column ("Members") that counts the non-zero values for each row. This would give something like the following:

Date, Members, Name1, Name2, Name3, Name4
1/1/2000, 3, -1, -1, 0, 1
1/2/2000, 2, 1, 1, 0, 0

I am having trouble using the count function to achieve this. In the cross tab query builder I am defining the following, where "Value" is the field that holds either a -1, 0 or 1.

Field: Members: Count([Value]<>0)
Table: <this is blank>
Total: Expression
Crosstab: Row Heading
Criteria: <this is blank>

But this always returns the count of all of the names. So in the above example I get the following:

Date, Members, Name1, Name2, Name3, Name4
 1/1/2000, 4, -1, -1, 0, 1
 1/2/2000, 4, 1, 1, 0, 0

as there are four names. For the first row it would ideally return 3, followed by 2 in the second row.

I would appreciate any help you all can give me on this one.

[FishVal]

Try to use Sum(Abs(<.. fieldname ..>)) instead of Count(<.. fieldname ..>).
Or write a custom aggregate function like in that thread.

Regards,
Fish.

[billelev]

Thanks - that's a great solution. I guess I would have to write a custom function if it generated values other than -1 and 1, though.

[FishVal]

I think it is not yet a case to write a custom function.
You can:

• Use an intermediate query to replace zeroes with Nulls and then perform the crosstab query.
• Use Sgn() function which returns -1 for negative, 0 for zero and +1 for positive numbers.

[gail chesler]

I want something similar, but the contents of the fields within the row are either null or not null. I want to count the number of cells in the row that are not null.

[akemmons2]

In your query if you do:
Field: Name: iif([Names]=0,Null,[Names])
Total: Count
Crosstab: Row Heading

Just did this on my Crosstab query and it works.