434,578 Members | 855 Online
Need help? Post your question and get tips & solutions from a community of 434,578 IT Pros & Developers. It's quick & easy.

Using Count within a Cross Tab Query

 100+ P: 119 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: Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers 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. Expand|Select|Wrap|Line Numbers Field: Members: Count([Value]<>0) Table:  Total: Expression Crosstab: Row Heading Criteria:  But this always returns the count of all of the names. So in the above example I get the following: Expand|Select|Wrap|Line Numbers 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. Jan 31 '09 #1
5 Replies

 Expert 2.5K+ P: 2,653 Try to use Sum(Abs(<.. fieldname ..>)) instead of Count(<.. fieldname ..>). Or write a custom aggregate function like in that thread. Regards, Fish. Jan 31 '09 #2

 100+ P: 119 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. Feb 2 '09 #3

 Expert 2.5K+ P: 2,653 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. Feb 2 '09 #4

 P: n/a 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. Oct 17 '10 #5

 P: 1 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. Jul 3 '19 #6