Survey Database Table Form Design

[DougMann]

Am working with small school doing survey. Have four basic types of
questions; multiple choice, multiple choice with Other Text Option,
Multi-Select Rank order, and simple text answer.

I saw and appreciated Duane Hookom's At Your Survey and am trying to figure
how to change to support "rank ordered multi-select" questions. Has anyone
worked with that survey and made modifications to support both the table and
the forms design changes.

I am very much under the gun as my own design seemed much less robust so I
discarded (I hope) in favor of trying to modify or find appropriate alternate
survey db design.

Appreciate any comments, suggestions or design options.

DataMann

 

[Rod Plastow]

Hi Doug,

I'm not familiar with your reference to 'Duane Hookom's At Your Survey' so
if there is anyone else out there who is please butt in.

Personally I would solve your problem by making all ranked questions as
subforms. As for designing the database I would define a table for each
ranked question. Each row in these tables contain two columns: the first
column is a sequence (consider autonumber or more likely you want control
over the sequence so make it an integer); the second column is the statement
text. Make the first column your primary key.

Now you need to specify a table that cross references the ranked question
and the main survey result table. Many people call these 'join' tables and
yet others use the term 'intersection.' Every row on this cross reference
table joins one interviewee with one statement and holds that interviewee's
ranking of the statement. To keep things straightforward you need one cross
reference table for every ranked question.

The subform is based on the cross reference table and can retrieve the
statement text from the ranked question table.

If your surveys are large then you may have to fiddle a little to make
tabbing into and out of the subform as seamless as possible.

-----

OK the 'cludge' way is to simply design a set of integer fields into your
survey results table and enter the statement text (or an abbreviation) as the
label to these fields. However analysis is much more messy using this scheme.

Rod

 

[Rod Plastow]

Just found and downloaded Duane's mdb. There is on tblQuestion provision for
a three level question numbering scheme. tblResponse is a cross reference
table similar to my description in my previous post.

So you can use what I termed the 'cludge' solution and the subsequent
analysis is helped by the question number leveling. Define the same question
number for each statement in your ranked question and use a sequence for the
next level down. Response type is 7 (numeric).

Do you really need to modify his design at all to cater for your situation?

Rod

 

[Duane Hookom]

I would use multiple questions as options for the multiple rank. For instance
if you have a list of 10 foods and ask the user to identify their 5 favorites
from 5 being their very favorite to 4 as their next, etc.

Each of the 10 foods would be a question with a selection list of 5, 4, 3,
2, 1, 0. There is no easy way to handle someone giving all 5s or assigning >0
values to more than 5 foods. You could write your own queries or code to
validate the answers.

 

[DougMann]

Duane - Interesting idea to break out the questions. I presume since my
survey has already happened I could enter questions in database in that form
and build custom queries as you say. However I would think I could have
"nbr allowed responses" = 6 and have question possibly

However I wonder if I added say a table tblAnswers with two fields, answr_
nbr and answr_txt and tied it to existing tblResponses (w/foreign key
answr_nbr) could I not optionally include text boxt tied to only questions
where Other was permitted.

on the multi-select rank order fields I kind of understand what Rod was
trying to suggest but get confused looking at your "Survey Information" form
and the three levels of answers - exaclty what/how am I supposed to use
those. Can you clarify what the Lvl1, Lvl2, Lvl3.

Also, You have a host of seemingly great functionality but I wish I could
get a bit more documenation on it - not only db schema/dictionary but how
you've the "Objects" table in your code?

DataMann

 

[Duane Hookom]

You could create an "other" table and see if it works. You would need to
adjust some forms, queries, reports, etc.

The Lvl1, 2, and 3 are only to group similar questions. Most surveys seem to
have groups of questions. I wanted to allow the survey developer the ability
to identify these groups as levels.

There may be some "junk" left over in the survey MDB that shouldn't be there.