query on 3 tables?

[PhilippeL]

Hi everyone,

I'm currently trying to develop a little ASP-VB/Access application for in-house surveys, and can't figure out the following. I have 3 tables:

tblSurveys:
-surveyId
-surveyName

tblQuestions:
-questionId
-questionName
-answerId1

tblSurveyQuestions:
-surveyId
-questionId

Now, let's say i need to find the list of questionName for surveyId = 1. I've tried the following query:

SQLsurveyQuestions="SELECT tblQuestions.questionName FROM tblQuestions INNER JOIN tblSurveyQuestions USING questionId INNER JOIN tblSurveys ON tblSurveys.surveyId=1"

But i get an "unspecified error" message. Would it be possible to first query the tblSurveyQuestions table to find the list of questionId, put that in a recordset, and then use that recordset to query the tblQuestions table? Could this work or is it impossible to use multiple lines from a recordset in a query?

I know that the normal way to do this would be to put a surveyId field in the tblQuestions table and forget about the tblSurveyQuestions table, but we'll be re-using the same questions over and over in different surveys, which is why there is a third table.

Any suggestions on how to do this?

Thanks in advance,

Philippe

[WebJoe]

Hi PhilipL, and welcome to WebmasterWorld

I think standard ANSI SQL would be:

SQLsurveyQuestions="SELECT " _

"tblQuestions.questionName " _

"FROM " _

"(tblQuestions LEFT JOIN tblSurveyQuestions " _

"ON tblQuestions.questionID = tblSurveyQuestions.questionID) " _

"LEFT JOIN tblSurveys " _

"ON tblSurveyQuestions.surveyID = tblSurveys.surveyID" _

"WHERE tblSurveys.surveyId=1"

[PhilippeL]

Hi Webjoe,

Bingo, it's alive! Thanks a bunch!

Now how about this: Now that I have the question names and answer Ids, I also need to find out the answer names related to these answer Ids. So we have:

tblSurveys:
-surveyId
-surveyName

tblQuestions:
-questionId
-questionName
-answerId1 (and 2, 3, 4, 5)

tblSurveyQuestions:
-surveyId
-questionId

tblAnswers:
-answerId
-answerName

Could I start from the previous recordset to query the tblAnswers table (is it even possible)? Or should I start from the previously made query and add another "join"? I tried with the following query:

SQLsurveyQuestions="SELECT tblAnswers.answerId, tblAnswers.answerName 
FROM ((tblAnswers LEFT JOIN tblQuestions ON tblAnswers.answerId = tblQuestions.answerId1) 
LEFT JOIN tblSurveyQuestions ON tblQuestions.questionID = tblSurveyQuestions.questionID) 
LEFT JOIN tblSurveys ON tblSurveyQuestions.surveyID = tblSurveys.surveyID 
WHERE tblSurveys.surveyId=1"

It works fine, but (obviously) it only gives me the results for tblQuestions.answerId1, and not the other answerIds (2, 3, etc). How could I manage to get all the answerIds in there? Should I loop the query, or is there a more direct approach?

The results might look like something close to this:
Question 1: Are you satisfied?
1) Yes
2) No

Question 2: Rate the level of service
1) Great
2) Good
3) Bad

Thanks again for any help,

Philippe

[sun818]

You can experiment with your queries in Access in Design View. Then once you have the results you want, go into SQL View and copy/paste the SQL you find there.

[PhilippeL]

Thanks for the tip, it was about time i discovered this! It will probably help me in the future.

Unfortunately, even with this i still can't figure out my problem. It works well until i add the tblAnswers table. So i made a test query (in Access design view) with only these 2 tables:

tblQuestions:
-questionId
-questionName
-answerId1
-answerId2 (and so on, up to 5)

tblAnswers:
-answerId
-answerName

And strangely it always returns empty results, even though there is relevant data in these tables.

Basically, there is a 1->many relationship from tblAnswers.answerId to tblQuestions.answerId1, 2 and so on (so that's 5 relationships, all from the same field). I noticed that when I leave only 1 relationship, I get results, but as soon as I add 1 more, the results are empty.

Perhaps it's because i'm not familiar with how Access executes queries yet, but i can't figure out why it's returning empty results. Any probable explanations?

Thanks,

Philippe

[txbakers]

I'm having a similar problem with Access at the moment.

It doesn't like to include more than one "on" clause in the FROM for left joins.

I'll let you know when I solve it.

[john_k]

If each answer in tblAnswers is specific to one question, then you should add a QuestionId field.

If each answer can be associated to more than one question, then you should add a new many-to-many table tblQuestionAnswers (just like your tblSurveyQuestions).

Then remove the AnswerId1 (2, 3, 4, and 5) column from the tblQuestion table. Redundant columns like this give it a denormalized arrangement anyway and are not a good design.

[john_k]

Sorry - I meant to include this with the previous suggestions: Add a "Sequence" (or similarily named) column to then control the order of the answers.

[john_k]

Here's an example:

tblSurveys:
-surveyId
-surveyName

tblQuestions:
-questionId
-questionName

tblSurveyQuestions:
-surveyId
-questionId

tblAnswers:
-answerId
-answerName

tblQuestionAnswers
-questionId
-answerId
-sequence

Then, to get all questions for a survey, along with all answers in proper order:

SELECT A.QUESTIONID, B.QUESTIONNAME, C.ANSWERID, D.ANSWERNAME 
FROM tblSURVEYQUESTIONS AS A 
   INNER JOIN tblQUESTIONS AS B ON (A.QUESTIONID = B.QUESTIONID) 
   LEFT JOIN tblQUESTIONANSWERS AS C ON (B.QUESTIONID = C.QUESTIONID) 
   LEFT JOIN tblANSWERS AS D ON (C.ANSWERID = D.ANSWERID) 
WHERE (A.SURVEYID = 1) 
ORDER BY B.QUESTIONID, D.SEQUENCE 

[PhilippeL]

Thanks for the suggestion John, it works fine with the additional table (i had to tweak the query a bit but it does work now).

Your help was most welcomed!

[PhilippeL]

Ok here's a new question...

Now i'm trying to build reports out from the answers from users. I need to use the following tables:

tblQuestions:
-questionId
-questionName

tblAnswers:
-answerId
-answerName

tblSurveyQuestions:
-surveyId
-questionId

tblQuestionAnswers
-questionId
-answerId
-sequence

tblUsersAnswers:
-userId
-surveyId
-questionId
-answerId

Basically, for a certain surveyId, I need the question Ids & names, answer Id & names and total count for each answer. The results might look like:

Q1 - Are you happy?
A1) Yes - 5
A2) No - 4
A3) Maybe - 0

So far, the query I have gives that total, but only for answers which have a total count of 1 or more. Here's the query:

SQLsurveyQuestions="SELECT Count(A.answerId) AS TotalAnswers, A.answerId, A.questionId, B.questionName 
FROM tblUsersAnswers AS A 
RIGHT JOIN (tblQuestions AS B 
RIGHT JOIN tblSurveyQuestions AS E ON B.questionId=E.questionId) ON A.questionId=B.questionId 
WHERE E.surveyId=1 
GROUP BY A.answerId, A.questionId, B.questionName 
ORDER BY A.questionId, A.answerId"

Obviously, tblAnswers and tblQuestionsAnswers are still missing from the equation, and all my attempts at integrating them with JOIN have failed. Any advice?

Thanks,

Philippe