Chuck said:
Good morning,
I am working on a DB that has response data in 12 fields (resp01..resp12)
per record. I have been asked to accum the values and a count for non-blank
(not null) fields. I want to do this in a query. I appreciate your input.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Of course, you know that that table is not in Normal form? This makes
the query harder to create.
The design should have question_number and question_response columns:
customer_id question_no question_response
=========== =========== =================
999 1 yes (or whatever the response is)
255 2 no
888 3 no
.... etc. ...
Then you could count the Null/Not Null responses like this:
SELECT Sum(IIf(question_response IS NULL,1)) As Null_Response,
Sum(IIf(question_response IS NOT NULL,1)) As Responded
FROM <table name>
.... etc. ...
In the current configuration of your table you'll have to do this
kludge:
SELECT IIf(resp01 IS NULL,1,0) +
IIf(resp02 IS NULL,1,0) +
IIf(resp03 IS NULL,1,0) +
...
IIf(resp12 IS NULL,1,0) As Null_Response,
IIf(resp01 IS NOT NULL,1,0) +
IIf(resp02 IS NOT NULL,1,0) +
IIf(resp03 IS NOT NULL,1,0) +
...
IIf(resp12 IS NOT NULL,1,0) As Responded
FROM <table name>
.... etc. ...
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQxYUP4echKqOuFEgEQK+eACg9p5PT4T6MVynDI5aer2zmAprrYAAoPqu
zmEG1Ush13uFAVy0WE9lCGxF
=0mSk
-----END PGP SIGNATURE-----
