Firstly don't make the mistake of including three Boolean (Yes/No) columns in
the table, one for each alternative answer to a question. It is often done,
but it’s a very bad design.
You have two alternative approaches really, one is to use an unbound form
with check boxes and use code in the form's module to update the values in a
column in the table. You'll also have to write code to check the right boxes
on the basis of existing data in the table when navigating to an existing
record, and to ensure that only one of each alternative can be checked at one
time. With this approach you can design a form to mirror a paper form with
all questions and possible answers set out together.
An alternative is to use a bound form with an option group, with check boxes
as its controls, bound to the AnswerID column. This avoids writing code to
write the values to the table as an option group's value is a number. In
addition to this the form would include a control bound to the QuestionID
column. This control can be a combo box which shows the text of the
question, but whose value is the underlying QuestionID. This form would be
based on the table which stores the respondent's answers to each question,
one row for each question and the answer given. Assuming each question can
only be answered once the QuestionID would be the primary key.
With the second approach, rather than showing all questions and answers
together, in simulation of a paper form, you'd show each question and the
option group bound to the AnswerID column individually. You can write code
so the form steps through the questions in order, moving to the next question
after the respondent answers one, i.e. moving to a new record with the
question in place in the AfterInsert event procedure of the previous record
entered. This does assume, however, that each question has the same set of
answers, so it is quite restrictive and, while it is easier to set up than
the first approach using an unbound form the appearance from the respondent's
point of view is not ideal. One drawback is that the respondent cannot se
the full extent of the questionnaire at the outset, so doesn't know just how
big a task they are embarking on, so its important to give the respondent
information on this, e.g. "Question 5 of 25", as they proceed . Arising from
this it requires some code to pick up where they left off if they close the
form and reopen it. While it can be done, I'd advise the use of an unbound
form as this, albeit requiring more work to develop, does give you complete
flexibility in how you present the questionnaire to the respondent, and
projects a more professional image of your organization.
As regards the underlying model, in addition to the table which stores the
respondent's answers to each question, you'd need a separate Answers table
with columns AnswerID and Answer, the latter containing the text values. So
it might read:
1 Important
2 Somewhat Important
3 Not Important
You'd then have a Questions table with QuestionID and Question columns.
The Questions table is related to the Answers table in a many-to-many
relationship, so you'd then need a QuestionAnswers table with columns
QuestionID and AnswerID to model this relationship, each row representing a
question and one of the possible answers to it.
Ken Sheridan
Stafford, England
