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 and answerId1 for surveyId = 1. I've tried the following query:

But i get a syntax error message, most likely because of the "WHERE" clause. 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.

Anyone suggestions?

Thanks in advance,

Philippe

Ok i found that the following query gives the right results:

Now my problem is that i need to get the answer names related to the anser Ids found with this query.

So i have the following tables:

tblSurveys:
-surveyId
-surveyName

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

tblSurveyQuestions:
-surveyId
-questionId

tblAnswers:
-answerId
-answerName

I manage to get part of the answer with this query:
But it can only give me the answer names for answerId1. How can I modify this to integrate answerId2, 3, etc? Should i use a loop on the query, or can i use the previously obtained recordset with a query only on the tblAnswers table?

Thanks for any help!

Philippe