[AccessD] Design question

Hi Drew

Thanks for your time in offering a full reply to my question.
I take your points on board.  I think in this instance as the
requirements are for a one-off
project (2/3 questionnaires for a specific survey - 100 or so fields
with) I will treat the multiple options as questions/fields in their own
right but appreciate your schema suggestion for a larger project.
I am coding using Vb.net/asp.net for this app.
Thanks
Richard

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
Sent: 17 January 2008 17:25
To: Access Developers discussion and problem solving
Subject: Re: [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.


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-----------------------------------------------------------------
Why not visit our website www.bury.gov.uk
-----------------------------------------------------------------
The information contained in this e-mail and any files transmitted
with it is for the intended recipient(s) alone. It may contain
confidential information that is exempt from the disclosure under
English law and may also be covered by legal,professional or other privilege.
If you are not the intended recipient, you must not copy, distribute or take any
action in reliance on it. 
If you have received this e-mail in error, please notify us immediately by using 
the reply facility on your e-mail system.
If this message is being transmitted over the Internet, be aware that it may be 
intercepted by third parties.
As a public body, the Council may be required to disclose this e-mail or any 
response to it under the Freedom of Information Act 2000 unless the information
in it is covered by one of the exemptions in the Act. By responding to this
e-mail you accept that your response may be subject of recording/monitoring to
ensure compliance with the Council's ICT Security Policy. 
Electronic service accepted only at legalservices at bury.gov.uk and on fax number 
0161 253 5119 .
*************************************************************