Maria DiGiano wrote:
I am using Access to organize data from a survey which uses a Likert
scale to measure response- the scale is 3 points- "I agree", "I don't
know" and "I disagree". The numerical value of each response (1, 2 or
3) varies- such that for some questions "I agree" is = to 3 points,
and other times "I agree" is = to 1. I created an "answer key" table,
such that each record is a question, and for the fields of "I agree",
"I disagree" and "I don't know" I enter the appropriate numerical
value. I also have the "survey results" table, in which each record
is a respondent, and the questions are in fields. I want to create a
combo box/ drop menu for each question so that the data entry person
can simply choose "I agree", "I disagree" or "I don't know"- I want to
link the combo box for each question to the "answer key" table so that
I can record the numerical value of each response in a separate field
in the "survey" table. How do I link the two tables, so that each
question's answer key is linked to survey table? I would greatly
appreciate some help on this- as it is making me want to drop out of
grad school!
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
It would have helped if you showed the design of your tables. If I did
the design it would look like this:
Surveys:
survey_nbr
survey_name
survey_start_date
survey_end_date
... other survey info ...
The primary key is the survey_nbr.
Questions:
survey_nbr (linked to Surveys.survey_nbr)
question_nbr
question_text (the actual question)
The primary key is survey_nbr & question_nbr.
AnswerKeys:
survey_nbr
question_nbr
(survey_nbr & question_nbr link to Questions
survey_nbr & question_nbr)
answer_value (the numeric value assigned the answer)
answer_text
The primary key is the survey_nbr, question_nbr
The AnswerKeys table data would look like this when the "I agree" value
equals 3 and the "I don't know" value equals 1:
survey_nbr question_nbr answer_value answer_text
1 5 3 I agree
1 5 2 I disagree
1 5 1 I don't know
On paper the question would look like this:
5. I found the level of service excellent.
I agree__ I disagree__ I don't know__
The ComboBox RowSource would look like this:
SELECT answer_value, answer_text
FROM AnswerKeys
WHERE survey_nbr = [Form].survey_nbr
AND question_nbr = [Form].question_nbr
The reference [Form]. points to the values on the form on which the
ComboBox resides. You can also use [Form]! (use the exclamation point
instead of the "dot") only if there are controls with the same names.
These values have to be present on the form for the ComboBox to work.
These values should be from the Questions table, which should be part of
the form's RecordSource.
Set up the ComboBox's properties like this:
Column Count: 2
Column Widths: 0", 3"
Bound Column: 1
Limit To List: Yes
Only the answer_text, "I Agree," etc., will show.
The answer_value is the data that would be stored in the Respondents
table.
For every new question the ComboBox will have to be requeried.
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBSNhVsIechKqOuFEgEQKikACghGOW35K+YIapUGpBZn8Pfr CqGZkAoLrg
K0h4W/fDVMDRVobIhvxfWPhT
=Wwjv
-----END PGP SIGNATURE-----