Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Nov 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    New Lounger
    Join Date
    Nov 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •