Likert Scale data- how to enter in Access

[Struggling graduate researcher]

I have created a database to record responses to a questionnaire in which
respondents must answer YES, NO or I Don't know for each question. For EACH
question, their response gets a different weight- for example, sometimes a
YES response = 3, sometimes = 1. SO I have created an "answer code table" in
which each question has a unique ID, and then I entered the correct values
for YES, NO, and I DON"T KNOW responses. Then I created a "survey" table
record responses, where each survey has a unique ID.
SO now what I want to do, is link the answer key table with each survey
response, so that I can easily enter the survey data and calculate each
respondents score based on the answer key with minimal error. Basically for
each question I wanted to have a drop down box with the three choices and
then have the numeric code automatically calculated in a separate record in
the table. I can't seem to figure it out. Please help!!

 

[Dale Fye]

Well, from the sounds of your post, it sounds like you have a single record
for each respondant, and a column for each response. Is that correct? If
so, then you are "committing spreadsheet" and need to restructure your data.

Post back with the structure of your Response and LikertValues tables and
I'm sure someone will be able to help you.

For a good tutorial on surveys, take a look at:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.


"Struggling graduate researcher" <Struggling graduate
(e-mail address removed)> wrote in message
news[email protected]...

 

[Struggling graduate researcher]

Thanks so much your response.
So what exactly does "committing spreadsheet" mean? Yes, you hit the nail
on the head- I currently have a single record for each respondent, and each
response is a column. How should I restructure the data? Have each response
as a separate record? I certainly don't want to commit spreadsheet, whatever
it is!
As I mentioned in the original post, the Likert values are in a separate
table, where each record is a question and stores the value assigned to each
response category (ie if "I agree"= 3 or if "I agree"=1).
Maybe restructuring my data will make the relationship that much clearer.
Thanks

 

[Dale Fye]

"Committing spreasheet" is creating column names that are actually data
points (in your case survey item #s). A lot of times, people who are used
to working with spreadsheets just assume databases should be configured the
same way, but to take advantage of the relational aspects of a database, you
have to turn that on its head. Another good example is budgeting. Many
people create spreadsheets that have expenditure categories down the left
column of a spreadsheet, and have months listed across the top, and they
enter either estimated or actual expenses in the cells of the matrix. In a
relational database, you would generally set this up as 3 columns
(ExpenseType, ExpenseDate, and Amount). Then you can use queries to present
the data in a wide variety of formats (including a crosstab query, which
looks like your spreadsheet).

If done properly, your survey responses will go in a table that looks
something like:

RespondantID, QuestionNum, Response

Then, your LikertScale table would have columns like"
QuestionNum, Response, ResponseValue

Then you can create a query:

SELECT RespondantID, SUM(ResponseValue) as CumScore
FROM tblResponses
INNER JOIN tblLikertScale
ON tblResponses.QuestionNum = tblLikertScale.QuestionNum
AND tblResponses.Response = tblLikertScale.Response
GROUP BY tblResponses.RespondantID

This would give you each respondants ID and their cumulative score.

Or you could create a query:

SELECT QuestionNum, Response, Count(RespondantID) as Frequency
FROM tblResponses
GROUP BY QuestionNum, Response

To get a quick count of the number of each response to each question. To do
this with your "spreadsheet" structure would be much, much more difficult.

HTH
Dale

"Struggling graduate researcher"

 

[Struggling graduate researcher]

Thank again- currently restructuring the data as you recommended. HOwever, I
am still confused by your recommendation for the LikertTable structure.

IF I just have questionNum, Response, and responseValue as my three fields-
what I need to do is have a lookup box for response- with YES, NO, and Dont
Know- and then is there a way to create an expression for EACH question- "if
YES" then 3, "if No" =1? This is an important point, as I said, not all
questions carry the same value for YES.

Ex. for QuestionNum 1, if YES=1, if DONT KNOW=2, if NO=3,
but for QuestionNum2, if YES=3, if Don't Know= 2, if NO=1.

Also, I would like to have the ResponseValue field in the survey response
table, so that the VALUE also appears.

I am not as much interested in finding the cumulative score, but rather
making it easy for someone to enter the data- so that they just choose in the
look-up table YES, NO, Don't KNOW- without worrying about if YES=3 or if
YES=1. I want it to automatically come up.

THanks for your patience