Re: How to work with 'multiple response sets' in Access

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi Tina,

That was a real lesson on data base design; very, very useful! Thank you
very much.
I studied your remarks on the structure of the database in parallel with
Duane Hookom's sample database, and that was really useful.
The structure is complex, but it gets simpler if I have only one survey
(then I don't need the Surveys table), and simpler still if all my questions
are of the true/false type (because then I don't need the Options table) - am
I correct?
I understand that transferring the data from a spreadsheet style table to
the normalized database has to be done one column a time; I had hoped that
there would be a shortcut, but alas!

Now for my third question. After having normalized my database, following
your suggestions, I want my users to be able to go on answering my 60
questions as if nothing has changed, simply by clicking on a number (between
0 and 60) of radio buttons. So what do I have to do to make that work, now
that my 60 questions are no longer linked to separate fields? Pat, in his
first post, suggested there were two ways: creating subforms using a combo
box or by means of VBA.
I don't see how a subform would solve this problem. On the other hand, a
combo box with 60 different options to choose from doesn't seem very elegant.
I'm not afraid of a little bit of programming, but I definitely need some
help here.
Pat also mentions something called a multi-select listbox. That does not
seem to be something that comes standard with the control toolbox, so you
would have to explain me how to create that. But I think the same objection
applies here: a listbox doesn’t seem handy for choosing from 60 different
options!
Could you offer me some help on this aspect?
Thank you very much.

"tina" wrote:

okay, let's see if i can offer something helpful, here. i'll base my
suggestions on the following, taken from a previous post in this thread:

Suppose I have my questionnaire data
(several hundreds of records) in a flattened table, 60 columns long, all
of
the yes/no data type, and I want to transform them into a normalized
table.
Is there a way to do this, without having to type in everything for a
second
time? I suppose this has to be done by means of a query, but I just can't
figure out how. Can you help?

basically, you need a table of questions, one record for each question. if
you have 60 different questions, one for each column in the spreadsheet,
then the table will hold 60 records, one question per record. Sixty is not
so many, i'd just enter the questions manually - it'll probably be quicker
than trying to migrate that data programmatically.

you need a table of answer *options*. if all 60 questions have the same
three answer options, then this table will only have three records. if some
questions will have different options than others, then you have a
many-to-many relationship between questions and options: one question may
have many answer options, and one option may be available for many
questions. in this case, your options table should list all possible
options, one record for each option. then you'll need a third table to link
options to questions - each record will store one question/answer option
combination. so a question with three answer options will have three records
in this table; a question with five answer options will have five records in
this table, etc.

then you need a table to store the actual options chosen for each question,
by each survey respondent. you'll also need a table to store data that is
*survey-specific*, rather than answer-specific, because you don't want
repeating data stored with each answer on a particular completed survey. so
your answers table will include a foreign key pointing back to a particular
completed survey, and a foreign key pointing back to a particular question
option (that linking table described in the paragraph above). note that you
do *not* need a foreign key pointing to a record in the questions table,
because that key value is already stored in the linking table.

again, you really should study Duane Hookom's sample survey database to see
how this is all laid out and connected.

okay, that's basically the tables you'll need. migrating the data from a
spreadsheet format will be ugly, no doubt about it - i wouldn't be surprised
if it takes an entire day, or two, to do it. once you have the questions
table, options table, and question-options table set up, and the spreadsheet
data imported into its' own table, you should stop and back up your
database. that way if you mess up and have to start over, you have a clean
"original" to copy and begin again.

add a primary key field to the spreadsheet table (an autonumber field will
do fine). now each survey record is uniquely identified. use an Append query
to copy the *survey-specific* data into the surveys table, making sure that
you also append the primary key field.

you'll have to migrate each answer column in the spreadsheet table
*separately*. you're appending answer data from the spreadsheet table into
the answers table; make sure you include the primary key field in the
append. set criteria on the spreadsheet column you're working with: "yes"
if it's a Text data type, True if it's a Yes/No (True/False) data type.
manually enter the primary key value of the appropriate answer option from
the answer options table, to be appended to the answer field in the answers
table. the end result will be that, for the spreadsheet answer column you
working with, all surveys having a Yes answer to that question will be
migrated into the answers table using the appropriate key value from the
answer options table. for instance, out of the 6000 records in the
spreadsheet table, if 3010 answered yes in the first answer column, then the
query will append 3010 records into the answers table.

once you've migrated the first column of answer data from the spreadsheet
table into the answers table, stop and check it for accuracy. create a
Select query, linking the surveys table, the answers table, and the question
options table, and the questions table. pull the survey fields, and the
question option fields, and the questions fields into the grid, and view it
to make sure you're seeing the correct answer for the correct question, for
the appropriate number of survey records.

if you're only doing this migration once, i wouldn't bother writing a
separate Append query for each column and saving the query, etc. just write
an Append query to handle migrating the first answer column, run it, then
modify it to handle the second answer column, run it, etc.

this is hard to explain in the abstract, so i hope you at least got an idea
of how to do it, if not all the (fuzzy) details.

hth



.

Relevant Pages

  • Re: How to work with multiple response sets in Access
    ... repeating data stored with each answer on a particular completed survey. ... spreadsheet format will be ugly, no doubt about it - i wouldn't be surprised ... add a primary key field to the spreadsheet table (an autonumber field will ... you also append the primary key field. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: joining/merging two columns into one
    ... A common approach to importing data which needs to be "scrubbed" to fit into ... You can use an append query to concatenate/combine the two address ... I have modified the spreadsheet to fit the Access field ... the pre-existing Access database has one column for ...
    (microsoft.public.access.externaldata)
  • Append Query in MS Access
    ... have a make table query running which essientially copies Table A, ... Table C which I would like to append to Table B. The problem I ... I have to update several queries in this database ... with the same survey ID in the survey ID field of each query. ...
    (microsoft.public.access.queries)
  • Re: Simple Question re: Input box for query which selects Table to get data from
    ... Then use two tables in your database... ... and *APPEND* each day's data into the ... Access isn't a spreadsheet. ... store data in tablenames. ...
    (microsoft.public.access.gettingstarted)
  • append query
    ... I am trying to upload data from a spreadsheet in to my ... database. ... want to append to the relevant table. ...
    (microsoft.public.access.queries)