Questionnaire in MS Access

[krygsma]

So, I need to figure out how to do what I want to do with Access. I
have many questions with mutually exclusive options, each option has a
value, never=0, few times=1...ect.. (then when questions are
"reversed", never will equal 4, which can be handled later but I'm open
to any ideas) but need to be displayed as text. The easiest solution to
this, I thought, would be option groups, perfect, they look beautiful,
it seems quite logical that they can simply be connected to a table.

Data in table associated with one form, which participant enters needs
to look like this.

subjectnumber Q1 Q2 Q3 Q4 .....
120001 4 2 0 3 .....
120002 3 0 1 2 .....
....
....

The question then is how do I connect the option groups to a table. I
want the numbers that show up in the datasheet view saved in an access
table. However if I connect the table via record source, the questions
disappear in form view. I would like to know if there is any way to
connect the two? Would it be a query of some sort, select bla from
datasheet bla and put it in table bla? (Sorry, I'm not up on the lingo)
Or does option group not transfer into a table, becuase you can't do it
when you go the other way (from table to form).

If the answer is no, there is no way, then...
Do the questions have to be list boxes or combo boxes?... where the
options are looked up in a table and then you have the text in a table
but then how does that get converted into numbers before exporting into
Excel or SPSS?

So then the data in the table would look like:

subjectnumber Q1 Q2 Q3 Q4 .....
120001 very often sometimes never often .....
120002 often never few times sometimes .....
....
....

How would I convert to

subjectnumber Q1 Q2 Q3 Q4 .....
120001 4 2 0 3 .....
120002 3 0 1 2 .....
....
....

Any thoughts or information would be greatly appreciated.

This may be a simple question for any Access expert but I am not one of
those. All of the help and examples have to do with business solutions,
most of which are fairly straightforward and very little on option
groups.

[Douglas J. Steele]

Sorry, but I think you're going about it wrong. The questions shouldn't be
separate fields in the same row: rather, each question should be a separate
row, so that rather than

subjectnumber Q1 Q2 Q3 Q4 .....
120001 4 2 0 3 .....
120002 3 0 1 2 .....

you'd have

subjectnumber Question Answer
120001 1 4
120001 2 2
120001 3 0
120001 4 3
120002 1 3
120002 2 0
120002 3 1
120002 4 2

(you can use a cross-tab query to present the data in the original format)

To see a well designed example, take a look at Duane Hookom's "At Your
Survey"
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%20Your%20Survey%2 097'
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%20Your%20Survey%2 02000'

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

<kr*****@mcmaster.ca> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

So, I need to figure out how to do what I want to do with Access. I
have many questions with mutually exclusive options, each option has a
value, never=0, few times=1...ect.. (then when questions are
"reversed", never will equal 4, which can be handled later but I'm open
to any ideas) but need to be displayed as text. The easiest solution to
this, I thought, would be option groups, perfect, they look beautiful,
it seems quite logical that they can simply be connected to a table.

Data in table associated with one form, which participant enters needs
to look like this.

subjectnumber Q1 Q2 Q3 Q4 .....
120001 4 2 0 3 .....
120002 3 0 1 2 .....
...
...

The question then is how do I connect the option groups to a table. I
want the numbers that show up in the datasheet view saved in an access
table. However if I connect the table via record source, the questions
disappear in form view. I would like to know if there is any way to
connect the two? Would it be a query of some sort, select bla from
datasheet bla and put it in table bla? (Sorry, I'm not up on the lingo)
Or does option group not transfer into a table, becuase you can't do it
when you go the other way (from table to form).

If the answer is no, there is no way, then...
Do the questions have to be list boxes or combo boxes?... where the
options are looked up in a table and then you have the text in a table
but then how does that get converted into numbers before exporting into
Excel or SPSS?

So then the data in the table would look like:

subjectnumber Q1 Q2 Q3 Q4 .....
120001 very often sometimes never often .....
120002 often never few times sometimes .....
...
...

How would I convert to

subjectnumber Q1 Q2 Q3 Q4 .....
120001 4 2 0 3 .....
120002 3 0 1 2 .....
...
...

Any thoughts or information would be greatly appreciated.

This may be a simple question for any Access expert but I am not one of
those. All of the help and examples have to do with business solutions,
most of which are fairly straightforward and very little on option
groups.

[krygsma]

Actually, in order to analyze the data the way we do, I need it in
this format. Are you saying it would be better database design (more
compact or something?) to present the data your way and then transpose
it to my way at the end? I have hundreds of questions and multiple
forms so the question is relevant.
All I had to do was select both the number and the word (but only
showing the word in the form of course) and specify the associated
field in the table as a number and it all works beautifully with combo
boxes or list boxes. The option group will not transfer the numbers to
a table, I have no idea why but it doesn't matter, I did the questions
over in combo boxes and they look better and take up less space.

Thank you for the links I will investigate them further shortly.

Douglas J. Steele wrote:

Sorry, but I think you're going about it wrong. The questions shouldn't be separate fields in the same row: rather, each question should be a separate row, so that rather than

subjectnumber Q1 Q2 Q3 Q4 .....
120001 4 2 0 3 .....
120002 3 0 1 2 .....

you'd have

subjectnumber Question Answer
120001 1 4
120001 2 2
120001 3 0
120001 4 3
120002 1 3
120002 2 0
120002 3 1
120002 4 2

(you can use a cross-tab query to present the data in the original format)
To see a well designed example, take a look at Duane Hookom's "At Your Survey"
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%20Your%20Survey%2 097' http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%20Your%20Survey%2 02000'
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

<kr*****@mcmaster.ca> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

So, I need to figure out how to do what I want to do with Access. I
have many questions with mutually exclusive options, each option has a value, never=0, few times=1...ect.. (then when questions are
"reversed", never will equal 4, which can be handled later but I'm open to any ideas) but need to be displayed as text. The easiest solution to this, I thought, would be option groups, perfect, they look beautiful, it seems quite logical that they can simply be connected to a table.
Data in table associated with one form, which participant enters needs to look like this.

subjectnumber Q1 Q2 Q3 Q4 .....
120001 4 2 0 3 .....
120002 3 0 1 2 .....
...
...

The question then is how do I connect the option groups to a table. I want the numbers that show up in the datasheet view saved in an access table. However if I connect the table via record source, the questions disappear in form view. I would like to know if there is any way to
connect the two? Would it be a query of some sort, select bla from
datasheet bla and put it in table bla? (Sorry, I'm not up on the lingo) Or does option group not transfer into a table, becuase you can't do it when you go the other way (from table to form).

If the answer is no, there is no way, then...
Do the questions have to be list boxes or combo boxes?... where the
options are looked up in a table and then you have the text in a table but then how does that get converted into numbers before exporting into Excel or SPSS?

So then the data in the table would look like:

subjectnumber Q1 Q2 Q3 Q4 .....
120001 very often sometimes never often .....
120002 often never few times sometimes .....
...
...

How would I convert to

subjectnumber Q1 Q2 Q3 Q4 .....
120001 4 2 0 3 .....
120002 3 0 1 2 .....
...
...

Any thoughts or information would be greatly appreciated.

This may be a simple question for any Access expert but I am not one of those. All of the help and examples have to do with business solutions, most of which are fairly straightforward and very little on option
groups.

[Douglas J. Steele]

In my opinion, it's definitely better to normalize the data as I suggested.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

<kr*****@mcmaster.ca> wrote in message
news:11*********************@l41g2000cwc.googlegro ups.com...

Actually, in order to analyze the data the way we do, I need it in
this format. Are you saying it would be better database design (more
compact or something?) to present the data your way and then transpose
it to my way at the end? I have hundreds of questions and multiple
forms so the question is relevant.
All I had to do was select both the number and the word (but only
showing the word in the form of course) and specify the associated
field in the table as a number and it all works beautifully with combo
boxes or list boxes. The option group will not transfer the numbers to
a table, I have no idea why but it doesn't matter, I did the questions
over in combo boxes and they look better and take up less space.

Thank you for the links I will investigate them further shortly.

Douglas J. Steele wrote:

Sorry, but I think you're going about it wrong. The questions

shouldn't be

separate fields in the same row: rather, each question should be a

separate

row, so that rather than

subjectnumber Q1 Q2 Q3 Q4 .....
120001 4 2 0 3 .....
120002 3 0 1 2 .....

you'd have

subjectnumber Question Answer
120001 1 4
120001 2 2
120001 3 0
120001 4 3
120002 1 3
120002 2 0
120002 3 1
120002 4 2

(you can use a cross-tab query to present the data in the original

format)

To see a well designed example, take a look at Duane Hookom's "At

Your

Survey"

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%20Your%20Survey%2 097'

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%20Your%20Survey%2 02000'

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

<kr*****@mcmaster.ca> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

> So, I need to figure out how to do what I want to do with Access. I
> have many questions with mutually exclusive options, each option has a > value, never=0, few times=1...ect.. (then when questions are
> "reversed", never will equal 4, which can be handled later but I'm open > to any ideas) but need to be displayed as text. The easiest solution to > this, I thought, would be option groups, perfect, they look beautiful, > it seems quite logical that they can simply be connected to a table. >
> Data in table associated with one form, which participant enters needs > to look like this.
>
> subjectnumber Q1 Q2 Q3 Q4 .....
> 120001 4 2 0 3 .....
> 120002 3 0 1 2 .....
> ...
> ...
>
> The question then is how do I connect the option groups to a table. I > want the numbers that show up in the datasheet view saved in an access > table. However if I connect the table via record source, the questions > disappear in form view. I would like to know if there is any way to
> connect the two? Would it be a query of some sort, select bla from
> datasheet bla and put it in table bla? (Sorry, I'm not up on the lingo) > Or does option group not transfer into a table, becuase you can't do it > when you go the other way (from table to form).
>
> If the answer is no, there is no way, then...
> Do the questions have to be list boxes or combo boxes?... where the
> options are looked up in a table and then you have the text in a table > but then how does that get converted into numbers before exporting into > Excel or SPSS?
>
> So then the data in the table would look like:
>
> subjectnumber Q1 Q2 Q3 Q4 .....
> 120001 very often sometimes never often .....
> 120002 often never few times sometimes .....
> ...
> ...
>
> How would I convert to
>
> subjectnumber Q1 Q2 Q3 Q4 .....
> 120001 4 2 0 3 .....
> 120002 3 0 1 2 .....
> ...
> ...
>
> Any thoughts or information would be greatly appreciated.
>
> This may be a simple question for any Access expert but I am not one of > those. All of the help and examples have to do with business solutions, > most of which are fairly straightforward and very little on option
> groups.
>