Table Design: Tracking Several Questionnaire's

[EMS]

Good afternoon,
I am currently working on an Access DB that tracks several Client Survey Questionnaire's. I recently attended a DB Development training session. The trainer suggested that I track the answers to ALL questionnaire's on one table. Here is sample list of field names (per his suggestion):
RespondentID
QuestionnaireID
QuestionID
Response

Currently, the way that I have it set up is I have all the different questionnaires each on seperate tables. I'm having a hard time wrapping my mind around his suggestion for several reasons: 1) The answers (data types) to the questions vary, some are multiple choice answers, some are yes/no, several connect to lookup tables, etc. 2) I wouldn't be sure how to set up a form that would accomodate such a set up.

On the other hand I can definitely see a few advantages to such a set-up, for one it would be easier querying and setting up automated reports if all answers were contained on one table.

Thanks

 

[Duane Hookom]

You can download "At Your Survey" from
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. This
demo shows how all of this is possible.

--
Duane Hookom
MS Access MVP

Good afternoon,
I am currently working on an Access DB that tracks several Client Survey

Questionnaire's. I recently attended a DB Development training session.
The trainer suggested that I track the answers to ALL questionnaire's on one
table. Here is sample list of field names (per his suggestion):

RespondentID
QuestionnaireID
QuestionID
Response

Currently, the way that I have it set up is I have all the different

questionnaires each on seperate tables. I'm having a hard time wrapping my
mind around his suggestion for several reasons: 1) The answers (data types)
to the questions vary, some are multiple choice answers, some are yes/no,
several connect to lookup tables, etc. 2) I wouldn't be sure how to set up a
form that would accomodate such a set up.

On the other hand I can definitely see a few advantages to such a set-up,

for one it would be easier querying and setting up automated reports if all
answers were contained on one table.