How to work with 'multiple response sets' in Access

Hi Tina, thanks once again for your help.
I downloaded, renamed and opened the file without any problem.
However, your solution, using a subform in datasheet view, is not what I was
having in mind. Sorry. What I would like to have is 60 checkboxes together
with their labels on a form just as they would appear if they were linked to
individual fields in a (spreadsheet style) table. On clicking on the
checkboxes, the answers would get stored in the underlying, normalized table
(i.e., in an intersection table containing the respondent number and the
question number as foreign keys - I am assuming here we work with only one
survey and that all questions are boolean yes/no data type).
Can this be done? Maybe only by using VBA?

"tina" wrote:

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" wrote in message
...
OK, Tina, let's suppose all my questions are boolean and let's work with
simple checkboxes.

"tina" wrote:

comments inline.

"Wim" wrote in message
...
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

i'm way too lazy to do it
programmatically unless i absolutely have to!

this doesn't fall into the category of "absolutely have to" for me, so
sorry, hon, you're on your own. maybe somebody else will help. if you don't
get a response in this thread in a day's time, suggest you repost - with
specifics from this thread that will bring folks up to speed on where you
are now. you can include a link to this thread, but i don't recommend that
you count on people being willing to read this entire thread in order to
help you. good luck with your project.

hth


"Wim" wrote in message
...
Hi Tina, thanks once again for your help.
I downloaded, renamed and opened the file without any problem.
However, your solution, using a subform in datasheet view, is not what I
was
having in mind. Sorry. What I would like to have is 60 checkboxes together
with their labels on a form just as they would appear if they were linked
to
individual fields in a (spreadsheet style) table. On clicking on the
checkboxes, the answers would get stored in the underlying, normalized
table
(i.e., in an intersection table containing the respondent number and the
question number as foreign keys - I am assuming here we work with only one
survey and that all questions are boolean yes/no data type).
Can this be done? Maybe only by using VBA?

"tina" wrote:

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" wrote in message
...
OK, Tina, let's suppose all my questions are boolean and let's work
with
simple checkboxes.

"tina" wrote:

comments inline.

"Wim" wrote in message
...
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

OK, fair enough! You have been of great help anyway.
I'll just wait if Pat still has something to say on this topic. If not, I'll
follow your suggestion. Cheers!

"tina" wrote:

i'm way too lazy to do it
programmatically unless i absolutely have to!

this doesn't fall into the category of "absolutely have to" for me, so
sorry, hon, you're on your own. maybe somebody else will help. if you don't
get a response in this thread in a day's time, suggest you repost - with
specifics from this thread that will bring folks up to speed on where you
are now. you can include a link to this thread, but i don't recommend that
you count on people being willing to read this entire thread in order to
help you. good luck with your project.

hth


"Wim" wrote in message
...
Hi Tina, thanks once again for your help.
I downloaded, renamed and opened the file without any problem.
However, your solution, using a subform in datasheet view, is not what I
was
having in mind. Sorry. What I would like to have is 60 checkboxes together
with their labels on a form just as they would appear if they were linked
to
individual fields in a (spreadsheet style) table. On clicking on the
checkboxes, the answers would get stored in the underlying, normalized
table
(i.e., in an intersection table containing the respondent number and the
question number as foreign keys - I am assuming here we work with only one
survey and that all questions are boolean yes/no data type).
Can this be done? Maybe only by using VBA?

"tina" wrote:

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" wrote in message
...
OK, Tina, let's suppose all my questions are boolean and let's work
with
simple checkboxes.

"tina" wrote:

comments inline.

"Wim" wrote in message
...
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

If you want your Access application to look like a spreadsheet, you'll need
to pay the price. And that price is lots of VBA code because you'll need to
use unbound forms. I don't have an example to give you. I have always been
able to convince clients that the cost and limitations of making Access work
like Excel are not worth the benefit (no change to their existing
interface). If they really want Excel interfaces, they should stick with
Excel. As Tina said - good luck with this project.

"Wim" wrote in message
...
OK, fair enough! You have been of great help anyway.
I'll just wait if Pat still has something to say on this topic. If not,
I'll
follow your suggestion. Cheers!

"tina" wrote:

i'm way too lazy to do it
programmatically unless i absolutely have to!

this doesn't fall into the category of "absolutely have to" for me, so
sorry, hon, you're on your own. maybe somebody else will help. if you
don't
get a response in this thread in a day's time, suggest you repost - with
specifics from this thread that will bring folks up to speed on where you
are now. you can include a link to this thread, but i don't recommend
that
you count on people being willing to read this entire thread in order to
help you. good luck with your project.

hth


"Wim" wrote in message
...
Hi Tina, thanks once again for your help.
I downloaded, renamed and opened the file without any problem.
However, your solution, using a subform in datasheet view, is not what
I
was
having in mind. Sorry. What I would like to have is 60 checkboxes
together
with their labels on a form just as they would appear if they were
linked
to
individual fields in a (spreadsheet style) table. On clicking on the
checkboxes, the answers would get stored in the underlying, normalized
table
(i.e., in an intersection table containing the respondent number and
the
question number as foreign keys - I am assuming here we work with only
one
survey and that all questions are boolean yes/no data type).
Can this be done? Maybe only by using VBA?

"tina" wrote:

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" wrote in message
...
OK, Tina, let's suppose all my questions are boolean and let's work
with
simple checkboxes.

"tina" wrote:

comments inline.

"Wim" wrote in message
...
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

Pat, I am sorry, but I disagree with you. I am not trying to make Access work
as if it were Excel, I am just trying to make things easier for the user.
Let’s forget the survey and think of a different example. An insurance
company wants applicants for a health insurance to fill out a form on which,
among other things, they ask “from which of the following diseases have you
suffered�; follows a list of 60 diseases. The easiest solution for the user
would be to have 60 check boxes to choose from. A combo box or a list box of
60 diseases is just not practical. What happens behind the screen, how Access
stores the information, should not be the user’s problem.
What I really want is an option group, but is should be a “multi-option
option group�. That doesn’t sound too far-fetched to me!
Anyway, I started a new thread in the Access Forms discussion group
(http://www.microsoft.com/office/comm...cd06d1899b68);
let’s hope I get some fresh answers there.
Thanks for your help.


"Pat Hartman (MVP)" wrote:

If you want your Access application to look like a spreadsheet, you'll need
to pay the price. And that price is lots of VBA code because you'll need to
use unbound forms. I don't have an example to give you. I have always been
able to convince clients that the cost and limitations of making Access work
like Excel are not worth the benefit (no change to their existing
interface). If they really want Excel interfaces, they should stick with
Excel. As Tina said - good luck with this project.

"Wim" wrote in message
...
OK, fair enough! You have been of great help anyway.
I'll just wait if Pat still has something to say on this topic. If not,
I'll
follow your suggestion. Cheers!

"tina" wrote:

i'm way too lazy to do it
programmatically unless i absolutely have to!

this doesn't fall into the category of "absolutely have to" for me, so
sorry, hon, you're on your own. maybe somebody else will help. if you
don't
get a response in this thread in a day's time, suggest you repost - with
specifics from this thread that will bring folks up to speed on where you
are now. you can include a link to this thread, but i don't recommend
that
you count on people being willing to read this entire thread in order to
help you. good luck with your project.

hth


"Wim" wrote in message
...
Hi Tina, thanks once again for your help.
I downloaded, renamed and opened the file without any problem.
However, your solution, using a subform in datasheet view, is not what
I
was
having in mind. Sorry. What I would like to have is 60 checkboxes
together
with their labels on a form just as they would appear if they were
linked
to
individual fields in a (spreadsheet style) table. On clicking on the
checkboxes, the answers would get stored in the underlying, normalized
table
(i.e., in an intersection table containing the respondent number and
the
question number as foreign keys - I am assuming here we work with only
one
survey and that all questions are boolean yes/no data type).
Can this be done? Maybe only by using VBA?

"tina" wrote:

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" wrote in message
...
OK, Tina, let's suppose all my questions are boolean and let's work
with
simple checkboxes.

"tina" wrote:

comments inline.

"Wim" wrote in message
...
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