Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    52

    Question Unanswered: Query design and filling comboboxes with values

    TBLCHECKLISTS
    checklist_ID

    TBLANSWERS
    checklist_ID
    option_ID (this is the chosen option)
    question_ID

    TBLQUESTIONS
    question_ID
    label

    TBLOPTIONS (displays the possible answer options per question)
    option_ID
    label

    I want to show a continuous form with all my questions (i.e. one question per form detail), and the user must be able to see/enter/change the answer for a specific checklist.

    So, the datasource for my form is a query:

    SELECT tblchecklists.checklist_ID, tblanswers.question_ID as question_ID, tblanswers.option_ID,tblquestions.label from tblquestions inner join (tblchecklists inner join tblanswers on tblchecklists.checklist_ID=tblanswers.checklist_ID ) on tblquestions.question_ID = tblanswers.question_ID

    This query works, and gives me for a specific checklist all the questions ANSWERED. This means, if there is not yet an answer object for a specific question, the question will not be displayed. Off course, I want all the questions displayed because I want the user the answer them. How can I alter my query to do this is or there a better way to accomplish this?

    I have for each question a label, and a combobox with the possible options listed. The label can be taken directly from the query, but for the combobox, I use this procedure:
    cmboptions.rowsource="SELECT label from tbloptions where question_ID=" & me.question_ID
    cmboptions.requery
    Alas, it doesn't work. Where do I have to put this procedure (which event), or is there a better way to look up the possible values for each question and put them in this combo?

    All help is appreciated!

    Greetings,

    Philip

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    ...tblchecklists LEFT JOIN tblanswers ...

  3. #3
    Join Date
    Mar 2004
    Posts
    52
    Thank you for your fast reaction Mr Owen.

    Alas, the problem isn't completely solved yet.

    This is my new query (simplified to focus on the main problem)

    SELECT tblquestions.question_ID, tblanswers.option_ID
    FROM tblquestions LEFT JOIN tblanswers on tblquestions.question_ID=tblanswers.question_ID

    this gives me:

    question, [answer]

    However, what I finally want to have is a recordset of all my questions with their answers (if available, else show nothing: left join does this), PER checklist_ID.

    Suppose, I have three questions (A,B,C) that I ask to my clients, the result should be (if I query for checklist 1):

    checklist1;A;answer on A or empty
    checklist1;B;answer on B or empty
    checklist1;C;answer on C or empty

    I hope you can solve this.

    Thank you again,

    Philip

Posting Permissions

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