I have been pulling my hair out all day writing and rewriting statements, trying to pull the right info from this table. I'm hoping someone here can help.

I have a flat table that stores responses to a survey. The survey is seven questions with two responses to each question (sts and wts). It has the columns

questionID (identifies the records in sts and wts)
sts (stores responses to sts)
wts (stores responses to wts)
authuser (the user who completed the form)

The data looks like this
<pre>
q | sts | wts | authuser
________________________
1 | weekly | NULL | tbrown
2 | weekly | NULL | tbrown
3 | -7 | NULL | tbrown
4 | hourly | NULL | tbrown
5 | hourly | NULL | tbrown
6 | morn | NULL | tbrown
7 | hourly | NULL | tbrown
1 | NULL | weekly | tbrown23
2 | NULL | hour | tbrown23
3 | NULL | morn | tbrown23
4 | NULL | daily | tbrown23
5 | NULL | -7 | tbrown23
6 | NULL | -8 | tbrown23
7 | NULL | hourly | tbrown23
1 | -7 | weekly | tbrown46
2 | week | hour | tbrown46
3 | week | morn | tbrown46
4 | time | daily | tbrown46
5 | daily | -7 | tbrown46
6 | NULL | -8 | tbrown46
7 | NULL | hourly | tbrown46
</pre>

I need to count the number of users who responded just to sts and just to wts and those who responded to both. The results I am looking for in the example is
sts only= 1 (i.e. user tbrown)
wts only= 1 (i.e. user tbrown23)
both = 1 (i.e. user trown46)

To get the number of sts-only respondents, I tried writing
Select COUNT(DISTINCT authuser) AS Authuser
FROM mytable
WHERE wts IS NULL
but this returns any user that has wts = NULL for any of the seven responses

what I need is to return users where all seven of the wts responses are null and the sts responses are null or have value, and another query that satisfies the opposite ( sts = NULL and wts = value or NULL)

I then tried:
SELECT COUNT(authuser) AS authuser
FROM mytable
WHERE (questionID = 1 AND wts IS NULL) AND (questionID = 2 AND wts IS NULL) AND (questionID = 3... and so on for all seven questions, by the SQL Server 2000 Enterprise manager breaks the WHERE clauses down into (questionID = 1) AND (wts is NULL) and so on.The result set is 0 of course, because no line satisfies the questionID of 1,2,3,4,5,6, and 7

I'm getting a bit frantic. Can anyone help? Thanks

Try rearanging the table:

AuthUser | stsQ1 | stsQ2 | stsQ3 | stsQ4 | stsQ5 | stsQ6 | stsQ7 | wtsQ1 | wtsQ2 .... etc.

Then

SELECT DISTINCT AuthUser
FROM TableName
Where stsQ1 IS NULL AND stsQ2 IS NULL AND stsQ3 IS NULL AND stsQ4 IS NULL AND stsQ5 IS NULL AND stsQ6 IS NULL AND stsQ7 IS NULL

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Thanks Owen.
Table is already populated, so this is a nice afterthought; however, I was provided with two great solutions from users on tek-tips:

(Secretly, I structured the table this way because I wanted to figure out how to do batch inserts! Never anticipated the problems that would be caused by my SQL naivety.)

SELECT Type, COUNT(authuser) AS Cnt
FROM (SELECT 'STSOnly' AS Type, authuser
FROM dbo.tblTCLoad
WHERE wts IS NULL
GROUP BY authuser
HAVING COUNT(*) = 7
UNION ALL
SELECT 'WTSOnly' AS Type, authuser
FROM dbo.tblTCLoad
WHERE sts IS NULL
GROUP BY authuser
HAVING COUNT(*) = 7
UNION ALL
SELECT 'Both' AS Type, authuser
FROM dbo.tblTCLoad
WHERE authuser IN
(SELECT authuser
FROM dbo.tblTCLoad
WHERE sts IS NOT NULL) AND authuser IN
(SELECT authuser
FROM dbo.tblTCLoad
WHERE wts IS NOT NULL)
GROUP BY authuser) qry
GROUP BY Type

and...

SELECT authuser AS STS_ONLY, COUNT(sts) AS sts, COUNT(wts) AS wts
FROM dbo.tblTCLoad
GROUP BY authuser
HAVING (COUNT(sts) > 0) AND (COUNT(wts) = 0)

Wow! that is one mother of an SQL Statement.

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo