Its done by entering separate rows in a table for each response, not in
separate columns. You'd use three principle tables for this, though other
tables would probably be needed too as you'll see.
1. Questions: This table would simply be a list of all questions in a
Question column. You could use the text column of the question itself as its
primary key or you could have a surrogate numeric primary key column, most
probably an autonumber.
2. Answers: This lists all answers in an Answer column and would include a
QuestionID foreign key column referencing the primary key of Questions.
You'd use a unique AnswerID as the primary key here as you might have the
same answer to different questions, so they would not necessarily be unique
values. Note that the answers are text values not Boolean (Yes/No) values as
I suspect you might be using at present from your reference to check boxes.
3. Responses: This is where the response to the questions are entered and
would have columns; RespondentID (referencing a Respondents table. You'd
only need this column if you are recording the respondents rather than
gathering data anonymously. If the latter you might have instead a
QuestionnaireID referencing the key of a Questionnaire table, each row of
which would represent one respondent's answer session. This table could
contain columns for the date when the questions are answered etc); AnswerID
(referncing the primary key of Answers; Note that you don't need to include
a QuestionID column in this table as the Questioned is determined by the
AnswerID, so to do so would introduce redundancy.
4. Ideally the Answer column of the Answers table should reference the
primary key of another table, AnswerTypes say, to ensure that the same answer
to different questions cannot be entered inconsistently in the Answers table.
If you want to simulate the appearance of a paper questionnaire with check
boxes and/or option groups etc then you'd need to use an unbound form and
insert rows into the Responses table in code in the form's module, with a
'Confirm' button say. This means that you'd need to redesign the form if the
questionnaire changes of course.
A more flexible approach, but less intuitive for the user entering the data,
would be to have a Respondents or Questionnaire form with a Response subform
in it, based on the Responses table and linked on RespondentID or
QuestionnaireID. The subform would include a combo box bound to the
AnswerID column. This could either be multi-columned, listing each
question/answer combination, and sorted by Question then Answer, or
correlated with an unbound combo box of Questions (there is no QuestionID
column to bind a control to in this table, remember) so that a question would
be first selected in the unbound combo box, then an answer form a list
appropriate to that question in the bound combo box. You'll find a demo of
how to set up combo boxes like this at the following link. It deals with
geographical data, but the principle is the same:
http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps
Ken Sheridan
Stafford, England
