Okay, new day, new problem.
I want to get the count of record fields where a value is not = to ''.
However, I want to do this for 33 different record fields.
Currently, my query looks like this:

set rs = server.createobject("adodb.recordset")
rs.open "SELECT COUNT(FieldName) From TABLE WHERE NOT FIELDNAME = '' "

Is there a way to do this in one SQL query instead of making 33 different
record sets?

All help and ideas are appreciated. Thanks in advance.

--Ashley

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.377 / Virus Database: 211 - Release Date: 7/15/2002
 

UNION

That said, the fact that you want to do this for 33 different fields makes
me think that you don't have a properly normalised database structure.
Usually, you should be able to do this with 1 statement and a GROUP BY
clause...

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Young, Ashley" <Ashley.Young@c...>
Subject: [access_asp] Multiple Count on Multiple Fields


: Okay, new day, new problem.
: I want to get the count of record fields where a value is not = to ''.
: However, I want to do this for 33 different record fields.
: Currently, my query looks like this:
:
: set rs = server.createobject("adodb.recordset")
: rs.open "SELECT COUNT(FieldName) From TABLE WHERE NOT FIELDNAME = '' "
:
: Is there a way to do this in one SQL query instead of making 33 different
: record sets?
:
: All help and ideas are appreciated. Thanks in advance.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Ken,

  Let me explain what I'm doing and perhaps you can flesh out what I should
do more specifically.
  I've got an online survey with multiple parts. I've got each answer in the
survey as a field in my database. I want to get the number of responses for
each field, i.e. any fields that are not left blank. Can you tell me a
better way to orgranize my database? And if not, can you give me more detail
on how to use the UNION statement you suggested?

Thanks in advance,

--Ashley

-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Sunday, August 25, 2002 10:22 PM
To: Access ASP
Subject: [access_asp] Re: Multiple Count on Multiple Fields


UNION

That said, the fact that you want to do this for 33 different fields makes
me think that you don't have a properly normalised database structure.
Usually, you should be able to do this with 1 statement and a GROUP BY
clause...

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Young, Ashley" <Ashley.Young@c...>
Subject: [access_asp] Multiple Count on Multiple Fields


: Okay, new day, new problem.
: I want to get the count of record fields where a value is not = to ''.
: However, I want to do this for 33 different record fields.
: Currently, my query looks like this:
:
: set rs = server.createobject("adodb.recordset")
: rs.open "SELECT COUNT(FieldName) From TABLE WHERE NOT FIELDNAME = '' "
:
: Is there a way to do this in one SQL query instead of making 33 different
: record sets?
:
: All help and ideas are appreciated. Thanks in advance.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.377 / Virus Database: 211 - Release Date: 7/15/2002
 

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.377 / Virus Database: 211 - Release Date: 7/15/2002
 

I would not do it this way.

I would have a table of questions. I would have a table of users (or surveys or whatever). I would then have a special join table
that contained the QuestionID, the UserID and the result:

QuestionID    UserID      Result
------------  ----------  ----------
1             1           1
2             1           NULL
3             1           1
4             1           1
1             2           1
2             2           NULL
3             2           NULL
4             2           NULL

and your query would be:

SELECT
    QuestionID,
    COUNT(*) AS NumberOfAnswers
FROM
    Answers
WHERE
    Result IS NOT NULL
GROUP BY
    QuestionID
ORDER BY
    QuestionID

which would output:

QuestionID        NumberOfAnswers
--------------    -------------------
1                 2
2                 0
3                 1
4                 1

Hope that helps

Cheers
Ken


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message ----- 
From: "Young, Ashley" <Ashley.Young@c...>
To: "Access ASP" <access_asp@p...>
Sent: Tuesday, August 27, 2002 12:09 AM
Subject: [access_asp] Re: Multiple Count on Multiple Fields


: Ken,
: 
:   Let me explain what I'm doing and perhaps you can flesh out what I should
: do more specifically.
:   I've got an online survey with multiple parts. I've got each answer in the
: survey as a field in my database. I want to get the number of responses for
: each field, i.e. any fields that are not left blank. Can you tell me a
: better way to orgranize my database? And if not, can you give me more detail
: on how to use the UNION statement you suggested?
: 
: Thanks in advance,