How should I go about creating a survey with "complex questions"?

[mdProgrammer]

I have this old MS Access survey (created by another programmer) that's... let's just say I dread having to change or modify one question or more every year or so, which takes up to a month to do, not including testing.

It has...

- hard coded column names as the question (i.e., Q1, Q2, Q2A... which correspond to the queries mentioned below -- a change requires updating each one of them or adding new columns)

- Over 1,000 queries. 4 queries for EACH POSSIBLE ANSWER (not just question!), and some questions have up to 20 possible answers. Each set relates to a form where the user views a report by code number, both code numbers, by year, and quarterly. As if there weren't enough, there's queries to gather the counts of 1 question, then another query to group all of the counts together. So, this thing got big quick, not to mention reports run slowly.

- The code isn't very pretty, either (I think this was one of the other programmer's first projects) - lots of looping through controls to get values and such.

- The database has a seperate database for creating the actual surveys (which go out to clients, and come back), which is fed into this database. (this all begins with a monthly DTS package that collects the data to send)

I know about creating tables using normalization (I've done this before when I redid a simpler survey - works wonders! except for when it's on a WAN connecting to a 15 gig SQL server database), but this one has complex questions. For example...

Questions 1-3 are normal "select on a scale of 1 to 5" type questions. Number 4 goes something like this -

4 - "Answer yes or no..."
4a - if Yes, select an option from 1-5

4b - If no, do something else...
4b5 - if option 5 is selected ("Other, please enter"), provide a field to enter data. (there may be different fields for different options) These form options are hidden unless the respected yes or no option is selected, and the respected sub-question is selected (i.e., 4b and answers 2, 3 and 5 would bring up a seperate textbox).

So, not only are there simple "select 1-5" questions, but question with multiple levels and would require different controls. I'm thinking of doing something like:

Question_ID
Question_Parent_ID
Question_Title

I'd do controls, but I know that in order to dynamically add a control, the form has to be switched to design mode. Are there any sample databases with complex questions like this? I found one called AYS2000, but that shows all of the sub-questions at once.

 

[lameid]

I haven't thought it through completely but rather than adding controls, I would append records as the questions are answered and requery the screen to accomodate the multilevel questions.

The structure would be somewhat complex but you could have a table with parent questions, answeres and follow up questions based on the answer. So you can save the record on after update, append the appropriate follow up question and requery.

This puts all answers in the same table. You might be better off with two levels of answers (two tables). The answer probably depends on the reporting requirements. Remember that reporting can be a reason not to normalize. On the flip side, not putting things in 3rd Normal form will probably bite you somewhere along the line.

Let us know if you need more help.

Although I must say that the number of queries you describe means there must be something fundamentaly wrong with the design. The storage has to be cheaper for the company than your time.

 

[dhookom]

I found one called AYS2000, but that shows all of the sub-questions at once

I created At Your Survey so I know a little about it. Only a small section of questions would be presented at a time. If you wanted greater functionality, you would need to add code based on the question and the answer. The code would append more records to the answers table.

Duane
Hook'D on Access
MS Access MVP