Need help designing a SURVEY results-only database

[vangogh228]

Hello.

I apologize for the simplicity of this question. I used to be able to do a lot more complicated things in Access than this, but I had a stroke in December, 2002 and just can't remember VB code hardly at all any more and have to keep things a lot simpler. I'm back to using wizards for forms and report creation and have to keep my designs really simple or I forget what I've done. I appreciate your understanding.

I am working with someone (a small college) who wishes to run a snail-mail survey of their graduates. The questions will be something like this: "Which courses did you find most useful?" Then, there will be a list of the courses to choose from (bubble fill-in), and the responder can answer with as many as they wish for each question. There will be under 20 questions like this, all choosing from the list of courses. There will also be some questions (about a dozen) with answers that are specific to that question, again with bubble fill-in. Some of those will be "pick one only" type, and others will allow multiple answers. I also need a comment section to make note of any submitted comments.

My question is this: What is the best way to set up the tables and forms so that the person inputting the answers can most efficiently enter the information and retrieve it through reports that count the responses and show that number for each answer to each question? I feel like I have to make a table for each question, but that seems like overkill, and I don't think I could figure out the form needed to bring all that together. Would I create a separate form for each question's input?

Again, I thank you all for your help, and appreciate your patience.

Tom

 

[Fizzio]

This is one of the most complex Db to design effectively as it needs more tables than you think, especially if you allow multiple answers for a question.

In basics, you will need the following tables (at least)

tblSurvey (survey details - type, name, date etc...)
tblParticipant (Name, Age etc....)
tblSurveyQuestions (Question Info - number, text etc)
tblQuestionResponses (Possible responses to the Questions)
tblAnswers (What response(s) the Participant actually chose - this is the difficult part if you can have multiple answers per question)

It may be easier if you look for a program that will do this specifically as it is hard to do.

hth.

 

[Estuardo]

Its not easy

G'd evening:

I agree with Fizzio, this is no easy to do it. But if you want to make it the best possible, you will have to make 1 table per Question, which wont be complicated, since all keeps the same structure, you just have to copy paste the first table as need it.

The answer tables may have an structure like this
PKQ1
Q1Op1 tipo byte
Q1Op2
Q1Op3
Q1Op4
etc
Where PK is primary Key
Qx means Question and x the question number
Opx means Option and x the option number

To call them all in a form.
Well if you are working with *.mdb is not a problem either. you just have to build one query where you put all them together and build the form based on that query.

The easy way to make the input form could be:
1 A form with multiple pages and radio buttons or
2. A form with multiple tabs and radio buttons
3. Many forms: This is dividing the number of question to place by form and keep a nice looking


The rest is a mater of time (and patience)
will be a tedious job of build queries, to get the sums, deviations, distances etc, to show the statics

I really wish you luck, and i hope you get well soon

Best regards

Estuardo

 

[Rich]

You do not need one table per question.

 

[Estuardo]

Rich,

You're right, i made i misktake, it should be on table per answer.

thanks for the remark

Estuardo

 

[Fizzio]

I would be inclined to disagree with you there with one table per answer.
This allows absolutely no flexibility to create new surveys, add questions and responses easily and creates a rigid structure. Stats would actually not be that much easier to obtain with one table per answer and creates a non-normalised data structure with redundancy and duplication.

 

[Estuardo]

Fizzio:

I see your point, and you're right. What would you suggest?
This duplication, and denormalization, is somethng that i just dind't saw. What about adding a tblStudent and stablish the relation with the tblQuestions?
What do you think? how woud you do it? create new structure?

Regards


Estuardo

 

[Rich]

The PK from the student table becomes one of the foreign keys in the responses table, along with the pks from questions and answers

 

[Fizzio]

Here is a (very) basic structure for a start in Survey Design. It may not be perfect but it will give an idea on where to start (Rich I'd appreciate your comments)

 

[Fizzio]

Cheers Pat,

I like that change.
I found that on a telephone triage Db I had designed myself that that was what I found - a lot of Yes/No answers (therefore duplication and redundancy - oops!) I had not thought about this additional structural change.

The difficult thing I found was translating the structure into a robust form design and found I needed to use VB to populate the question responses, using an option group.

Obviously, if the survey needs the option for multiple selection, then a listbox or labels/checkbox approach will likely be needed.