Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unanswered: query on 3 tables?

    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:

    Code:
    SQLsurveyQuestions="SELECT tblQuestions.questionName FROM tblSurveyQuestions WHERE surveyId = 1
    INNER JOIN tblQuestions ON tblSurveyQuestions.questionId=tblQuestions.questionId"
    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

  2. #2
    Join Date
    Apr 2004
    Posts
    2

    solution found, but new problem

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

    Code:
    SQLsurveyQuestions="SELECT tblQuestions.questionId, tblQuestions.questionName 
    FROM (tblQuestions LEFT JOIN tblSurveyQuestions ON tblQuestions.questionID = tblSurveyQuestions.questionID)
    LEFT JOIN tblSurveys ON tblSurveyQuestions.surveyID = tblSurveys.surveyID 
    WHERE tblSurveys.surveyId=1"
    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:
    Code:
    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"
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •