SQL/Access Query, please help!

[fsusurfer]

Hi, I am trying to create a survey results display page. I have roughly 20 questions, each with a Yes/No response.

Q1 Yes No
Q2 Yes No
Q3 Yes No

and so on.. any help on how to display the results? Please let me know, Thanks!

 

[dhookom]

Does your table have fields and records that you could share with us? Also, how do you want to "display the results"? Can we specify the output format or did you have something in mind?

Duane
MS Access MVP

 

[fsusurfer]

I am just trying to tabulate the total number of Yes and No responses for each question. The table I am getting the responses out of is called "responses" Each question has a Yes or No answer. Questions are Labled as Q1, Q2, Q3 and so on..its pretty simple, I am just not a frequent SQL user.

Yes No
Q1 # #
Q2 # #
Q3 # #

 

[dhookom]

Please try again. I don't know if Q1, Q2, Q3 are fields or values stored in a table. A normalized survey would have one record for each answer. I am thinking your questions might be fields but I am not sure. You might want to check out a survey app with this type of report in "At Your Survey" found at

http://www.rogersaccesslibrary.com/OtherLibraries.asp.

Duane
MS Access MVP

 

[fsusurfer]

Q1, Q2, Q3 are fields in the table...any help you can provide would be great, thank you!

 

[fsusurfer]

Below I have how I come up with Q1's total yes responses, but I also need the total no responses for Q1, and the total yes and no responses for the rest of the questions.

SELECT COUNT(Q1)
FROM Responses
WHERE Q1='Yes'

 

[lespaul]

How to write the query depends on how your tables are set up! That's why Duane kept asking you about it. As database professionals we are going to assume that your tables are normalized and you have something like:

tblQuestions
QuestionID
Question

tblAnswers
AnswerID
Answer

tblSurveys
SurveyID
SurveyDate

tblSurveyAnswers
SurveyID
QuestionID
AnswerID

With data that looks like:
tblQuestion
ID Question
1 Do you like fish?
2 Do you like beef?
3 Do you like cheese?

tblAnswer
ID Answer
1 Yes
2 No

tblSurvey
ID Date
1 9/26/2003

tblSurveyAnswers
SurveyID QuestionID AnswerID
1 1 2
1 2 1
1 2 1

Now if your tables are set up like this then this may work to get the information you want:

SELECT COUNT(IIF(ANSWERID = 1, 1, 0) AS YES, COUNT(IIF(ANSWERID = 2, 1, 0) AS NO FROM TBLSURVEYANSWERS GROUP BY QUESTIONID

Now if your tables are set up differently, you can share your design and we'll see if we can come up with a different solution.



Leslie

 

[dhookom]

The response from LesPaul is fairly close to the structure in "At Your Survey" which I mentioned in a previous post. The download is ready to run from the moment it is downloaded for most simple surveys. You won't have to change a table, field, code, form,...

If fsusurfer can't or doesn't want to change the tables, a UNION query can be used in conjunction with a crosstab to provide the results required. Let us know if you need help with this.

Duane
MS Access MVP