New to Access

[Dal Jeanis]

If you properly normalize your tables, you don't need two tables for each questionnaire. You have one table to store ALL the questions, one table to store ALL the answers, one table with information about the questionnaires, and one junction table that connects each questionnaire to the specific questions that are on that questionnaire. Those four tables can service a thousand different questionnaires and surveys.



Oops. one more table which stores information about when the customer took the survey, so that that information can be linked to the answers.

Anyway, there's lots of sample survey databases out there to crib a design off of.

[jeffe333]

Let me see if I can provide a clear example of what I'm working on...

These questionnaires ask parents about their toddlers ability to verbalize certain words. There are hundreds of words listed on the questionnaire, so they're broken into categories (i.e., animals, household items, verbs, etc.) Each category is supposed to list the words in alphabetical order. In the questionnaire, under each category, there are various words listed, and the parents have three choices: They can bubble in a box that expresses that their child understands the word, they can bubble in a box that expresses that their child both understands and verbalizes the word, or they can not bubble in anything expressing that their child neither understands nor verbalizes the word.

In Access, there is a form for each section of the questionnaire. The order that the words are in on the form should be the same order that they're in on the questionnaire. However, this is not always the case. Some of the forms, for various categories, don't match up w/ the questionnaire. The problem is, the questionnaires that are currently being entered into Access are no longer the questionnaires that are being handed out to parents. What I'm thinking is, maybe I should wait until we're done inputting all of the current questionnaires, then change the forms to match w/ the questionnaires that are currently being offered to parents.

[Dal Jeanis]

Sure, until you get the current questionnaire all entered, leave it how you have it. However, in the long run, you need to normalize the tables to get sensible information out of them.

Look at it this way. On Questionaire # 73, Section E, word number one is "Apple". However, on Questionnaire #75, "Apple" is word number two in section D.

The question (or prompt) is "Apple". You store "Apple" in the question list, for example as prompt number #137, and in the junction table you store the information that allows

Junction table example
QuestionnaireID    73    75   
SectionNumber       E     D 
QuestionNumber      1     2  
QuestionID         137  137

Storing the information this way not only saves space, it also allows you to compare the results on the same prompt from different surveys, regardless of where they happened to be on the particular survey. If you don't normalize the database, that analysis will be nearly impossible to achieve. With a normalized database, it's almost trivial.