How would you set up a field in a table with this information?

[Guest]

Hi!

I have to create a table for populating from a number of survey questions
through dreamweaver mx 2004 using access 2003. How would you set up the
fields in the table with information such as :
question: deliverable timeliness?
+2 +1 0 -1 -2 NA

 

[Ed Warren]

I think this problem has been solved may times over, you may want to google
about for a solution, or someone else will know where to look

But:
Each 'survey' consists of a set of questions (1:M)
Each Question can have one and only one answer (1:1)
Each answer can be one of six responses (2 1 0 -1 -2 NA) (problem here is
mixing text and numbers so when you go to sum/average you will have
problems)

You may want an AllowedResponse table
ResponseID response
---------------------------
2 really good
1 kinda good
0 neutral
-1 kinda bad
-2 really bad
-999 not applicable
------------------------------
Then you can toss out the -999 values with a query and do your sums/averages
with a groupby query.

Table:
Survey

SurveyID (key) SurveyParticipant ..... etc. (stuff about the survey)

Table:
Questions
QuestionID(key) QuestionText

SurveyResponses:

SurveyID QuestionID ResponseID

Set Primary key to combination of SurveryID/QuestionID so you can get one
and only one response to a question in a survery.

In your form you will use the responseID to populate a lookup box for the
responses

Your tables might look something like:

QuestionsTable
QuestionID QuestionText
1 Is Access a useful product?
2 How do you like your current vehicle?
3 How do you rate the current CBS evening news anchor?

SurveyTable:
SurveyID Respondent SurveyDate
1 WebGirl 9/24/2005
2 TheGuy 9/25/2005

SurveyResponseTable:

SurveyId QuestionID ResponseID
1 1 1
1 2 0
1 3 -999
2 1 0
2 2 1
2 3 0

Ed Warren