[AccessD] Design question

Actually Richard, it depends on what you are designing.  If you are
creating a database for ONE questionnaire, then I would go with having a
field for each answer.  If you are creating a database for MULTIPLE
questionnaires, then I would take a different tactic.

In a single questionnaire system, the database design represents the
questionnaire and the data within represents the responses.  In a
multiple questionnaire system, the database design represents how to
build a questionnaire, and the data represents both the questionnaires
and the responses.

For example, in a multiple questionnaire system:

tblQuestionnaire:
QuestionnaireID
QuestionnaireName

tblQuestions:
QuestionID
QuestionnaireID
QuestionText

tblAnswerDefinitions
AnswerDefinitionID
QuestionID
Label
DataType (ie, 1 for text, 2 for yes/no)
SortOrder

tblAnswersText
AnswerDefinitionID
AnswerValue (As Text)

tblAnswersYesNo
AnswerDefinitionID
AnswerValue (As Yes/No)

One caveat here, is that in a design like this, Access is not the
optimum GUI for the interface, because the controls will need to be
dynamic (ie, in your 'Select the items that fit you' question, that
could have 1 item or 20 items....each item requiring a label and a
checkbox.)  Dynamic controls are inherent in VB, ASP, and .Net.  These
would be a better choice for the interface itself.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Griffiths,
Richard
Sent: Thursday, January 17, 2008 3:58 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Design question

Hi

 

I have a design question I'm looking for help with.

 

Scenario.....Applicant completing questionnaire.

 

Answer options vary...text reply, yes/no etc.

 

I'm not sure how to deal with questions when there are multiple replies 

e.g; tick which apply to you:

 

1.       Long hair

2.       Blue eyes

3.       Brown eyes

4.       Tall

5.       Short

6.       Old

7.       Young

 

The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI BusinessSensitve material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.