Survey database

[hockeyfan21]

Hi,

I am creating a survey database and cannot get the combo box for answers to tie into the questionid #.

I have 41 questions -
t_questions:
questionid
question

t_responseList
questionid
responsetext

t_responses
userid
questionid
responseid

I am trying to build a continuous form that will show all 41 questions, then, next to each question a combo box with the possible answers, which are different on almost all questions.

I have all the questions showing up in my form but all of my combo boxes are showing the answers for question 1.

Combo box rowsource:
ELECT t_responselist.responsetext, t_responselist.questionid FROM t_responselist WHERE (((t_responselist.questionid)=[Forms]![Form1]![questionid]));

[questionid] is a text box from the forms recordsource:
SELECT DISTINCTROW t_questions.questionid, t_questions.question FROM t_questions;

I've attached a screenshot of the form.

Thanks so much for any help,

Toni

 

[spikepl]

The problem stems from the combo having one and the same row source only, in all rows of your form. That is the nature of the thing and you cannot change that.

But you can still fiddle a solution. Pat has been so kind to provide a sample for one particular fix - have a look.

 

[dscudder]

Greetings:

I just finished a form like this. I took a different approach. I did not use 3 tables; I used two. The first has the respondent ID and the the questionnaire items as fields. The third is the response look-up table. (there could be a unique look-up table for each item.)

In very old programming, running on slow machines from he 90s, I have seen the questions and the responses in separate tables but the 2010 Oracle manual says this level of normalization is not needed for this anymore with the resources now available. Forty one columns is not a great many today. Mine has at least that many.

In any event, to return to your issue: in the the design view of the table with the questions and responses, you can link the question directly to the response look-up table using the look-up-wizard, which is a data type.. It does not matter whether the response tables are unique or whether one response box is linked to all the questions. It will then show up in the relationships tab. All questions are correctly linked to their respective response boxes.

When you place the field (questionnaire item) in the form, the response box goes along with it without error. Paste the text of the full question in the form rather than store is as a row in another table.

I don't know if this approach would work for you but it certainly worked well for me. The resulting data is easier to use too.