If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional sum
Hi, I have a number of fields that can either have 1 or 2. How can I sum just the 1's and ignore the 2's. The field names are from "p1q1A" to "p1q12A" cheers |
#2
|
|||
|
|||
Conditional sum
SELECT ABS((p1q1A=1) + ... + (p1q12A=1)) FROM tableName In Jet, a comparison evaluates to true, -1, or false, 0 (or to NULL). So, we just count the time it evaluates to true by adding the -1 and the 0, and finally, take the absolute value, to remove the negative sign. That expression also assumes that all the fields have a value, otherwise, a NULL will occur in the result of the comparison and the whole sum will also be evaluated to NULL. You can avoid this by: SELECT ABS( (Nz(p1q1A, 0) =1) + .... (Nz(p1q12A, 0) = 1) ) FROM tableName Hoping it may help, Vanderghast, Access MVP "scubadiver" wrote in message ... Hi, I have a number of fields that can either have 1 or 2. How can I sum just the 1's and ignore the 2's. The field names are from "p1q1A" to "p1q12A" cheers |
#3
|
|||
|
|||
Conditional sum
Its worked, thanks "Michel Walsh" wrote: SELECT ABS((p1q1A=1) + ... + (p1q12A=1)) FROM tableName In Jet, a comparison evaluates to true, -1, or false, 0 (or to NULL). So, we just count the time it evaluates to true by adding the -1 and the 0, and finally, take the absolute value, to remove the negative sign. That expression also assumes that all the fields have a value, otherwise, a NULL will occur in the result of the comparison and the whole sum will also be evaluated to NULL. You can avoid this by: SELECT ABS( (Nz(p1q1A, 0) =1) + .... (Nz(p1q12A, 0) = 1) ) FROM tableName Hoping it may help, Vanderghast, Access MVP "scubadiver" wrote in message ... Hi, I have a number of fields that can either have 1 or 2. How can I sum just the 1's and ignore the 2's. The field names are from "p1q1A" to "p1q12A" cheers |
Thread Tools | |
Display Modes | |
|
|