How to work with 'multiple response sets' in Access

[Wim]

I have (in an Access 2003 database) a series of fields of the yes/no data
type. I would like to transform them into fields that have the names/topics
of the existing fields as their possible answer categories.
Example: Field 1: listen to radio yes/no, Field 2: watch tv yes/no, Field 3:
read newspaper yes/no - to be transformed into Newfield 1, 2 and 3, all three
with options radio/tv/newspaper. A record containing yes - no - yes for the
initial fields would read radio - newspaper - NULL on the new fields.
(In SPSS this is called a "multiple response set" as the set of fields can
contain the answer to a single questionnaire question that allows more than
one response.)
If it is not possible to transform the existing fields, is there a way of
having my users fill out a form with simple checkboxes, but the answers being
stored in the kind of fields as I explained above?
Thank you for any suggestion.

 

[Pat Hartman \(MVP\)]

What you are asking for is called a "repeating group" in Relational database
speak. In a relational database, once you have more than one of something,
you have "many" and that requires a separate table. It is poor practice to
"flatten" the many items into many columns. One reason is that columns are
fixed and rows are not. If you add a column to a table, you'll also need to
do other maintenance such as modify queries, forms, reports, and code.
However, adding a row doesn't require any application changes. There are
several ways to implement your request. If you can upgrade to A2007 the
functionality is built in. Otherwise you'll need to build it yourself.
1. Codeless way - create a subform to show the selected values. Use a combo
to allow the user to choose.
2. VBA way - use a multi-select listbox to manage the data on a form.
However, since the multi-select listbox is not a bound control, you will
need to do all the reading/writing behind the scenes. When the user leaves
the listbox, you will need to add newly selected rows and delete no longer
selected rows from the many-side table.

 

[Wim]

Pat, thanks for trying to help me. I realise that I was actually asking
several questions in one. You were correct in pointing out that my questions
were based on an example that does not comply with basic rules for the design
of a relational database. So, let's start with that. My question is: what is
the big advantage of this set-up in a case where the data are relatively
static?

Let’s, for the sake of argument, presume I have a questionnaire, composed of
a series of questions, each allowing 1 answer, to be chosen among a group of
predefined answers.
The way to store the information for statistical analysis in e.g. Excel,
would be in one table, in which every line contains the answers to one
questionnaire and every column the answers to one question.
In Access, however, I understand that the correct way to store the same
information would be to have one table in which each line contains one
questionnaire’s answer to one question. Now let’s say (example 1) my
questionnaire has 60 questions (not unusual) and that I have 6000
questionnaires (also not an unusual case). That would mean that my table
would have 360,000 lines! That does not seem very practical to me.
Can you explain me what the big advantage is of storing the information this
way?

A slightly different situation occurs (example 2) when all of my 60
questions only allow for a “yes†or a “no†answer. In that case I could put
into my table only the “yesâ€-answers, leaving out the “noâ€-answers. If, on
average, every questionnaire had 10 “yesâ€-answers, that would reduce my table
to 60,000 lines, still a bit bulky.

Now let’s consider (example 3) a questionnaire with 60 questions in which
only one (not more, not less) can be answered “yesâ€. In that case one line
per questionnaire would be enough, stating only which of the 60 questions was
answered affirmatively. My table would be reduced to 6000 lines.

But I could use this same idea in example 2. Instead of one line stating
which of the answers received an affirmative answers, I would several. Again,
if the average number of affirmative answers per questionnaire were 10, my
table would be back to 60,000 lines.

The data I work with are not really from questionnaires, but they are
comparable in that the structure is very unlikely to change much over time.
The amount of data, however, is going to grow quickly.
Why should I use a relational database structure and if I need to, which of
the solutions I tried to describe above is the best?

Thanks again for your willingness to help.

 

[Pat Hartman \(MVP\)]

"That would mean that my table would have 360,000 lines! That does not seem
very practical to me.
Can you explain me what the big advantage is of storing the information this
way?"
The advantage is that you can analyze it easily. To work with the flattened
structure, you'll need a query for each separate column to analyze the data
so the more columns you have, the more queries you'll need. Not to mention
the nightmare of adding additional columns for future questionnaires.

"Now let's consider (example 3) a questionnaire with 60 questions in which
only one (not more, not less) can be answered "yes". In that case one line
per questionnaire would be enough, stating only which of the 60 questions
was
answered affirmatively. My table would be reduced to 6000 lines."
If only one question is answered in the first scenario, you would have only
6000 rows. The extra advantage of the normal structure is only populated
rows need to exist.

There is no question in my mind that rows wins over columns. You will just
have to try building queries based on both structures to convince yourself.
Don't forget that relational databases do not support functions that work
"across" the columns way that Excel does. In a relational query, functions
operate on a domain which is defined by the number of rows selected in a
query/table. If you wanted to determine which of the 60 columns contained
the "yes", you'd need to interrogate the 60 columns specifically by name.
With the normalized structure, you just have criteria that returns all the
rows for a particular survey - one criteria verses 60 nested if statements.
Give me the rows!!!!

If your questionnaire is sparse (meaning that not all questions need to be
answered), the normalized structure could likely take less overall space
than the flat structure even though it takes considerably more rows. Access
is capable of handling millions of rows efficiently.

 

[tina]

Wim, you might also benefit from studying the often-recommended sample
survey database created by MVP Duane Hookom, at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

hth

 

[Wim]

Thanks again, Pat, for answering my question. But I have to say I’m not fully
convinced yet.

As for the first part of your answer, I’d say it just shows that for
analysing data, Access simply isn’t a good choice. Fortunately it’s easy to
exchange data between Access and Excel. But imagine I have two columns with
numerical data and I want to calculate the correlation between them; that
would be a lot easier in the “flattened structure†than in what you call a
“normalized†structure. Also, I do not understand why you consider adding
columns such a “nightmareâ€. It doesn’t seem that complicated to me. And I
wouldn’t have to do it very often.

But let’s get to my second question. 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?

 

[Wim]

Thanks for the tip, tina, I will have a good look at it.

 

[tina]

actually, Access is an excellent and very powerful tool for data analysis.
but you can't expect to use it the same way that you use Excel. Excel is
specifically a flatfile data format, and Access is specifically a relational
data format. the tools available in each software program are optimized to
work with the data in its' particular format. if you prefer to work with a
flatfile format, you'd be better off to stick with Excel.

if you want to use the tools in Access to analyze your data, you need to
embrace the concept of a relational structure for the data. otherwise,
you're essentially wasting your time because you cannot fully, or easily,
leverage the power of Access when the data is stored in a flatfile
structure, period.

to answer your question about migrating data into a normalized table
structure: i'm sure it's do-able, but i don't think i can offer any
constructive suggestions until we share a common basic "relational"
language. suggest you read up on the basics of data normalization, so the
suggestions you get from me - or anyone else here - will make sense to you.
for more information, see
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

hth

 

[Wim]

Tina, thanks for your reply.
I have actually read quite a lot about the theory of relational databases,
entity-relationship modeling and the theory of normalisation. What I do not
have is much experience.
Also, I am not defending Excel or flatfile stucture as opposed to Access or
relational structure. I am sorry if I gave you that impression. For part of
the data I am working with, the relational structure seems to be the best
option. My doubts are about part of my data that have a structure comparable
to survey data.
So, please be so kind to tell me what suggestions you have for me...
Thanks.

 

[tina]

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

 

[Pat Hartman \(MVP\)]

Tina has done an excellent job of outlining what you need to create a
generalized survey application.

"As for the first part of your answer, I'd say it just shows that for
analyzing data, Access simply isn't a good choice."
--- As Tina has already pointed out, the way in which Access and Excel work
is very different. You will be very unhappy with Access if you try to make
it work like Excel.

"Also, I do not understand why you consider adding columns such a
"nightmare". It doesn't seem that complicated to me."
--- That's because you've never had to do it. When you add a column to a
table, you must modify your data entry forms, your queries, your reports,
and any code that needs to use that new column. You could be modifying a
considerable number of objects (which you then have to test) when with a
properly normalized structure, you would only be adding a row which would
require no maintenance to any objects at all. In addition, if this is a
multi-user application, you have the problem of distributing a new back end
(ALL multi-user applications that use Jet tables should be split into a
front end (forms, reports, queries, code, macros) and back end (only tables)
along with the new front end and that means a data conversion.

The conversion method explained by Tina is the one I would (have) use. If
you're only doing it once, just create an append query for the first column,
run it, verify that it works, and then modify it to append each additional
column.

 

[Wim]

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.

 

[Wim]

Thanks, Pat, for your answer. I agree, Tina did an excellent job.
Thanks for adding more detail on the problems that have to be solved when
creating a new field. I understand now why you prefer records over fields. My
database is not, but will be in the future, a multi-user application.
Your answers enabled me to improve the formulation of my question.
You might have something to say about the third part of my question (see my
answer to tina).

 

[tina]

comments inline.

Hi Tina,

That was a real lesson on data base design; very, very useful! Thank you
very much.

you're very welcome, glad it helped.

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?

yes, i'd agree with both of those statements. you'll need a table to store
each *survey* response, one record per response, a table listing all the
questions, and a table to store each answer to each question for each survey
response.

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!

yeah, it's a bear - but then data migration usually is!

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?

since you mention radio buttons, and True/False answer options, i'm guessing
that you envision two radio buttons to answer each question - one button for
True and the other button for False. to me, that implies that you want to
know whether or not each question is answered, or not, because the user
would have the choice of selecting the True option, or the False option, or
neither one. on the other hand, if you use a checkbox for each question, and
store the values in a Boolean (Yes/No) field in the answers table, then any
question that is not True is automatically False - there are essentially no
unanswered questions, because a skipped question has a False value - and so
there is actually no need to store the False answers, only the True answers.

i can tell you how to handle either scenario using a subform, and in fact
that's probably what i'd use myself - i'm way too lazy to do it
programmatically unless i absolutely have to! but i don't want to get into
specifics until i know which data scenario fits your purpose. post back and
let me know, please, and we'll go from there.

hth

 

[Wim]

OK, Tina, let's suppose all my questions are boolean and let's work with
simple checkboxes.

 

[tina]

okay. i haven't time to go into it this AM, Wim - got to get to work - but
i'll post back this PM.

 

[Pat Hartman \(MVP\)]

There are a couple of methods you can use for populating your answers
subform. If you want all questions presented whether they will be answered
or not or if all questions require an answer, then -
when the user starts a new survey, in VBA you will run an append query that
selects all the questions for this type of survey from the table that
defines survey questions and appends them to the survey answers table where
the answers will be stored. The Answers table will contain the foreign key
to the survey, the foreign key to the question, and a Boolean for the
answer. You would then requery the subform and it will show all the
questions waiting to be answered. The subform properties should be set to
not allow additions or deletions.
The second alternative which is more efficient but a little more work for
the user is used when not all questions need to be answered. In this case
you would provide a combo box in the subform and allow the user to choose a
question from the drop down (already chosen questions should be eliminated -
ask if you need the SQL). So the user chooses the question and then chooses
the answer. In this case I would use an option group with two buttons to
capture the answer rather than the checkbox suggested by the previous
example.

 

[tina]

the technique is basically as Pat described in his last post elsewhere in
this thread. i also include a Delete query in my design, to remove the False
answers, since you don't necessarily need to store those. i've built a
bare-bones db, in A97 and converted to A2000, to demonstrate the setup. i'll
load the A2000 version to my website tonight, where you can download it to
study it, if you want. note that the file extension is .bak; make sure you
change it back to .mdb *before* trying to open the db in Access. if you need
the A97 version, let me know and i'll replace the A2000 version with it.

i use this basic setup in a working db that i use daily at my job. i've
added some frills to my real-world db, such as the ability to re-load
"missing" questions by clicking a command button, after they've been
automatically deleted. one thing you need to remember is that the db should
be compacted regularly, because adding/deleting records will cause it to
bloat. post back if you have any questions.

hth

 

[Wim]

Thanks a lot. Could you give me the URL of your website?

 

[tina]

http://home.att.net/~california.db/instructions.html
scroll to the bottom of the page and click the "demo" link, and don't forget
to change the .bak extension back to .mdb. fyi, i had some trouble
downloading the db when using *Netscape* browser; i had to right-click the
link and choose Save Link Target As...

hth