# Thread: a count if function (2007)

1. ## a count if function (2007)

I have a series of questions in a table that have yes, no, N/A answers as text. I need to be able to count each individual answer per question. I have been able to create a query that will return one answer, but I don't know how to ask it to do the whole series. Do I have to do an individual query for each one? There are over 300 questions.

Here is what I have:

SELECT Count([Round 1].ANS1) As YesANS1
FROM [Round 1]
WHERE ((([Round 1].[ANS1])="Y"))

2. ## Re: a count if function (2007)

Is each question a separate field? If so, how do you handle over 300 questions? A table can hold only 255 fields. Otherwise, how are the answers stored?

3. ## Re: a count if function (2007)

Yes they are separate fields, but they are split between a few tables. I just figured out how to make it do the counts, and am now working on turning it into a percentage. Any suggestions there?

Here's what I have so far:

SELECT DCount("ANS1","Round 1","ANS1='Y'") AS ANS1Yes, DCount("ANS1","Round 1","ANS1='N'") AS ANS1No,
DCount("ANS2","Round 1","ANS2='Y'") AS ANS2Yes, DCount("ANS2","Round 1","ANS2='N'") AS ANS2No;

4. ## Re: a count if function (2007)

Thsi is going to be a LOT of work!
It would be better to set up the table like this:

<table border=1><td align=center>Respondent</td><td align=center>Question</td><td align=center>Response</td><td align=center>1</td><td align=center>1</td><td align=center>Y</td><td align=center>1</td><td align=center>2</td><td align=center>N</td><td align=center>1</td><td align=center>3</td><td align=center>Y</td><td align=center>..</td><td align=center>..</td><td align=center>..</td><td align=center>2</td><td align=center>1</td><td align=center>N/A</td><td align=center>2</td><td align=center>2</td><td align=center>Y</td><td align=center>2</td><td align=center>3</td><td align=center>N</td><td align=center>..</td><td align=center>..</td><td align=center>..</td></table>
You can then use a crosstab query to tally the replies by question. This setup also makes it very easy to add or remove questions.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•