Count Query

Discussion in 'ASP General' started by AJ, Jul 21, 2006.

  1. AJ

    AJ Guest

    Hi all,

    I have the following data:

    AnswerID SubmitID AnswerQuestion OptionSelected
    0001 1 1 1
    0002 1 1 2
    0003 1 2 1
    0004 1 3 1
    0005 1 4 1
    0006 2 1 1
    0007 2 2 1
    0008 2 3 1
    0009 2 4 1


    What i need to do is perform a count that ignores duplicate answers to a
    given question.

    IE: 0002 would be ignored

    The suggested Results would be:
    Question 1: 2 (not 3)
    Question 2: 2
    Question 3: 2
    Question 4: 2

    I know this wouldn't be a problem in SQL Server.

    However,i am using MS ACCESS; restricted by its limitations i am unsure of
    how to proceed.

    Any help appreciated...

    Cheers,
    Adam
     
    AJ, Jul 21, 2006
    #1
    1. Advertising

  2. AJ wrote:
    > Hi all,
    >
    > I have the following data:
    >
    > AnswerID SubmitID AnswerQuestion OptionSelected
    > 0001 1 1 1
    > 0002 1 1 2
    > 0003 1 2 1
    > 0004 1 3 1
    > 0005 1 4 1
    > 0006 2 1 1
    > 0007 2 2 1
    > 0008 2 3 1
    > 0009 2 4 1
    >
    >
    > What i need to do is perform a count that ignores duplicate answers
    > to a given question.
    >
    > IE: 0002 would be ignored
    >
    > The suggested Results would be:
    > Question 1: 2 (not 3)
    > Question 2: 2
    > Question 3: 2
    > Question 4: 2
    >
    > I know this wouldn't be a problem in SQL Server.
    >
    > However,i am using MS ACCESS; restricted by its limitations i am
    > unsure of how to proceed.
    >

    Start by creating this view/saved query (call it UniqueAnswers):

    Select AnswerQuestion,OptionSelected
    From Answers
    GROUP BY AnswerQuestion,OptionSelected

    Then create another query with this sql:
    Select AnswerQuestion,Count(*)
    From UniqueAnswers


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Jul 21, 2006
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Giovanni Azua

    generic count report for JPQL query

    Giovanni Azua, May 31, 2009, in forum: Java
    Replies:
    0
    Views:
    2,084
    Giovanni Azua
    May 31, 2009
  2. efelnavarro09
    Replies:
    2
    Views:
    968
    efelnavarro09
    Jan 26, 2011
  3. Mark

    SQL Query using COUNT

    Mark, Jun 29, 2004, in forum: ASP General
    Replies:
    5
    Views:
    240
  4. Ruby Quest

    Syntax for db count query needed

    Ruby Quest, Dec 28, 2006, in forum: Ruby
    Replies:
    4
    Views:
    161
    Ruby Quest
    Dec 29, 2006
  5. laredotornado

    Count results of a query?

    laredotornado, Feb 28, 2008, in forum: Ruby
    Replies:
    1
    Views:
    99
    Ilan Berci
    Feb 28, 2008
Loading...

Share This Page