count multiple fields of one table in one query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a table with more than 10 option groups as fields. I want count the number of each fields with value not "No" in one query. The detail is as below:

option value: yes: -1, no: 0, n/a: 1 CNBD: 2
Fields: option A, option B, option C, ...........
0 -1 2
-1 1 -1
2 0 1
*************************
result: 2 2 3

Anybody know how to do the query? Thanks in Advance!
 
SELECT Sum(IIF([Option A]=0,0,1)) as CountA,
Sum(IIF([Option B]=0,0,1)) as CountB,
...
FROM yourTable

In the query grid, do a totals query and put the statement as a calculated field
and then use SUM in the Totals cell
Field: CountA: Sum(IIF([Option A]=0,0,1))
Total: Sum
 
Whoops! My error. I should have said.

Field: CountA: IIF([Option A]=0,0,1)
Total: Sum

Glad you found a solution.
Thanks very much! If I use SUM in the Total cell, it doesn't work. But if changes to Expression, it works well!

John Spencer (MVP) said:
SELECT Sum(IIF([Option A]=0,0,1)) as CountA,
Sum(IIF([Option B]=0,0,1)) as CountB,
...
FROM yourTable

In the query grid, do a totals query and put the statement as a calculated field
and then use SUM in the Totals cell
Field: CountA: Sum(IIF([Option A]=0,0,1))
Total: Sum
Hi,

I have a table with more than 10 option groups as fields. I want count the number of each fields with value not "No" in one query. The detail is as below:

option value: yes: -1, no: 0, n/a: 1 CNBD: 2
Fields: option A, option B, option C, ...........
0 -1 2
-1 1 -1
2 0 1
*************************
result: 2 2 3

Anybody know how to do the query? Thanks in Advance!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top