Count Query

[AJ]

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

 

[Bob Barrows [MVP]]

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