Print unselected survey answers

[Kila]

I am working on a survey that uses report headers to sort & display the data from my survey. For Example, I have:
Survey Header (there are a number of different surveys)
Area Header (Different Departments use these surveys)
Question Header (Displays each question)
Answer Header (Displays the answers associated with each question)

For each answer, there are statistics for that answer on what percentage of surveyees selected that answer. My problem is that my report only displays answers that were selected. i.e., no answers say 0% if no one picked them, and the text of the answer does not show up at all. How do I fix this?

 

[Kila]

Error message

I tried this, but I got an error message when I tried to save it, "Join expression not supported." When I click the help button here, it says, Possible causes:

· Your SQL statement contains multiple joins in which the results of the query can differ, depending on the order in which the joins are performed. You may want to create a separate query to perform the first join, and then include that query in your SQL statement.
· The ON statement in your JOIN operation is incomplete or contains too many tables. You may want to put your ON expression in a WHERE clause.

Here is the current SQL in the query:

SELECT DISTINCTROW tblAnswers.AnswerNumber, tblSurveys.Survey, tblSurveys.SurveyID, tblWardClinic.WardClinic, tblArea.Area, tblService.Service, tblQuestionList.Question, tblQuestions.QuestionNumber, tblQuestions.QuestionID, tblAnswerList.Answer, tblAnswers.AnswerID, tblCompletedSurvey.EmployeeID, tblCompletedSurvey.PatientID, tblCompletedSurvey.Date, tblResults.ResultID
FROM (tblSurveys INNER JOIN (tblQuestionList INNER JOIN tblQuestions ON tblQuestionList.QuestionListID = tblQuestions.QuestionListID) ON tblSurveys.SurveyID = tblQuestions.SurveyID) INNER JOIN ((tblService INNER JOIN (tblArea INNER JOIN tblWardClinic ON tblArea.AreaID = tblWardClinic.AreaID) ON tblService.ServiceID = tblWardClinic.ServiceID) INNER JOIN (tblCompletedSurvey INNER JOIN ((tblAnswerList INNER JOIN tblAnswers ON tblAnswerList.AnswerListID = tblAnswers.AnswerListID) INNER JOIN tblResults ON tblAnswers.AnswerID = tblResults.AnswerID) ON tblCompletedSurvey.CompletedSurveyID = tblResults.CompletedSurveyID) ON tblWardClinic.WardClinicID = tblCompletedSurvey.WardClinicID) ON tblQuestions.QuestionID = tblAnswers.QuestionID
ORDER BY tblAnswers.AnswerNumber;


Thanks for your consideration!

 

[Kila]

???

I'm not quite sure what you mean. Here is a picture of the relationships:

 

[Kila]

Now what?

I am a bit confused as to how to proceed. You guys have been wonderful with assisting me every step of the way with this thing, as I have been learning VBA as I go with this project. Don't even ASK what it looked like before you guys stepped in. However, now I seem to be getting conflicting advice. Here is why it was suggested to me that I set it up the way it is now (as I understood it, I could have misunderstood), and than please suggest what I should do.

A large amount of the complexity is to allow for future changes. The folks I work for swear that they will not change the questions, or answers, but they always do (gotta love committees!!). Obviously, tblQuestionList and tblAnswerlist contain all the questions and answers. It was suggested that I do it this way to accommodate the fact that some answers are used several times (yes, no, I don't know, etc.) in the same survey AND in different surveys (there are a number of different surveys). tblQuestions associates the questions (which are also reused in different surveys) with the apporpriate survey and orders them in the order we want. tblAnswers associates the correct answers with the correct question, and orders them for each question (Confused yet?). Also, because the questions and answers are very wordy, it was suggested that I use this method to prevent the exessive bulk of saving the full text of every answer with the CompletedSurvey. Only the AnswerID would be saved. I am concerned that moving AnswerListID to tblQuestions will make it difficult to change the answers and the order of them. It may also make tblQuestions very large with all the redundant answers.

tblCompletedSurvey and tblResults exist seperately because each survey (which has its own ID for every person surveyed) has several answers that are selected by the surveyee. This means that Surveyee 51 (CompletedSurveyID) owns ResultIDs 77, 78, 79, 80, 81, etc. This can be used to identify exactly which answer and exactly which question for each. I am at a loss how to combine this with the tblAnswers. Please let me know what I should do!

I did try the join in both directions & got the same error message. It seems to be an SQL issue. How can I clerify my ON/WHERE statements? Thanks for your time and expertise. Another point of view is always helpful! I may very well be going the long way around, but I need to know why. Thanks.

 

[Kila]

Ah committees!

The committee could not decide whether or not they wanted to capture demographic data. Then they decided to keep it and add an optional name line so that they could contact folks who did leave their names. Just as an FYI, this database is for departmental data entry from paper forms. Another reason this field is there is to capture such data if necessary from future, different surveys using the same structure with a minimum of changes. Why recreate the wheel? Who KNOWS what they will want then!

I will try your suggestions on the SQL. Probably next week as I am going out of town tomorrow. Thanks for your help!!!