Yes - No - N/A questions

[DanG]

I have a database set up to do quality reviews on files

There are a number of questions with Yes, No, N/A as the possible outcome to
each question. Each question is set up on the table as a combo box which
feeds off tblAnswers which consists of 2 columns: 1st is autonumber 2nd is
the 3 answers possible (yes, no, N/A)

I am looking to create a query that calculates the # of yes, no, and n/a
answers for each question.

Please help me out if you can. I have been trying to figure out an
efficient way to do this.

PS - I am pretty new at writing code, so I'd like to see if anyone has good
suggestions/hints for me

 

[KARL DEWEY]

SELECT YourTableName.Column2Name, Count(YourTableName.Column2Name) AS
CountOfColumn2Name
FROM YourTableName
GROUP BY YourTableName.Column2Name;

 

[John W. Vinson]

I have a database set up to do quality reviews on files

There are a number of questions with Yes, No, N/A as the possible outcome to
each question. Each question is set up on the table as a combo box which
feeds off tblAnswers which consists of 2 columns: 1st is autonumber 2nd is
the 3 answers possible (yes, no, N/A)

I am looking to create a query that calculates the # of yes, no, and n/a
answers for each question.

Please help me out if you can. I have been trying to figure out an
efficient way to do this.

PS - I am pretty new at writing code, so I'd like to see if anyone has good
suggestions/hints for me

No code is needed, just a totals query.

The trick is that you don't *count* yes, no, n/a - you sum them instead.

Could you post the actual structure of your table? I gather that you have one
field per question, and that it is a Lookup field; there are two major design
flaws right off the bat, but it should still be possible.

Do take a look at an example of a properly normalized survey database:

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

 

[DanG]

Karl, thank you. Your example worked.

John, I am interested (obviously) in any design flaws I may have.

Each question appears as a fieldname (i.e. PhysApp_Floor) with Data Type set
as Number. Under Lookup it links each field to the aforementioned
tblAnswers.

Please let me know what I might be doing wrong. Any and all help is
appreciated from Karl, John and anyone else who might have some hints for me.

 

[DanG]

Re-posting my reply as I am not sure you will be notified as I put the last
one under Karl's note.
John, I am interested (obviously) in any design flaws I may have.

Each question appears as a fieldname (i.e. PhysApp_Floor) with Data Type set
as Number. Under Lookup it links each field to the aforementioned
tblAnswers.

Please let me know what I might be doing wrong. Any and all help is
appreciated from Karl, John and anyone else who might have some hints for me.

 

[John W. Vinson]

Re-posting my reply as I am not sure you will be notified as I put the last
one under Karl's note.
John, I am interested (obviously) in any design flaws I may have.

Each question appears as a fieldname (i.e. PhysApp_Floor) with Data Type set
as Number. Under Lookup it links each field to the aforementioned
tblAnswers.

Please let me know what I might be doing wrong. Any and all help is
appreciated from Karl, John and anyone else who might have some hints for me.

This is a common enough problem that I call it the "questionnaire trap". It's
a very natural thing to just add another column/field for each question.

But it is WRONG.

Think about it: suppose down the road you need to add two more questions. You
open your table in design view, change the structure of the table, save it;
change the design of all your queries; change the design of all your forms;
change the design of all your reports... oops! database corrupted, where's my
backup disk...

Data (such as the identity or text of a question) should go *in a field in a
table*, not in a fieldNAME.

The proper structure would have;

Questionnaires
QID <Primary Key>
<information about the person filling out this instance of the
questionnaire>

Questions
QuestionNo
QuestionText

Answers
QID <whose questionnaire is this>
QuestionNo <what question are they answering>
Answer <integer or text or whatever is appropriate in your case>

Rather than one FIELD per answer, you will have one RECORD per answer.

Do look at Duane's AtYourSurvey. It's quite an impressive app and example of
how this kind of data should be handled.

 

[DanG]

Thank you. I will give this a shot. The link you had added to your other
post was giving me an error. Perhaps the site moved? Can you check it and
re-post it if possible.

Thanks again

 

[John W. Vinson]

Thank you. I will give this a shot. The link you had added to your other
post was giving me an error. Perhaps the site moved? Can you check it and
re-post it if possible.

Hrm. Works for me. Try going to

http://www.rogersaccesslibrary.com/

and navigating down through "Other libraries" and "Duane Hookum" to the At
Your Survey database.

 

[DanG]

Got it!

Thanks

Hrm. Works for me. Try going to

http://www.rogersaccesslibrary.com/

and navigating down through "Other libraries" and "Duane Hookum" to the At
Your Survey database.