By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,578 Members | 855 Online
Bytes IT Community
+ Ask a Question
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

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
  1. Date, Name1, Name2, Name3, Name4
  2. 1/1/2000, -1, -1, 0, 1
  3. 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
  1. Date, Members, Name1, Name2, Name3, Name4
  2. 1/1/2000, 3, -1, -1, 0, 1
  3. 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
  1. Field: Members: Count([Value]<>0)
  2. Table: <this is blank>
  3. Total: Expression
  4. Crosstab: Row Heading
  5. Criteria: <this is blank>
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
  1. Date, Members, Name1, Name2, Name3, Name4
  2.  1/1/2000, 4, -1, -1, 0, 1
  3.  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
Share this Question
Share on Google+
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.

Jan 31 '09 #2

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

Post your reply

Sign in to post your reply or Sign up for a free account.