Calculating percentages of multiple choice fields.

[Barry Davidson]

I have created a Database in Access 2000, which holds enquiry data taken from questionnaires filled in by customers. I have a field that is multiple-choice i.e. where you, V-happy, happy, unhappy or V-unhappy with the service. I have created the database without any problem but want to produce a report that shows the percentage of people that where for example "Happy with the service" I cant seem to get my head round how to do this, I can create a pivot table that calculates the percentage of people who answered the question by summing the number of completed fields in the table and dividing that by the number of records in the database, but how do I drill down and get percentages of individual answers from a particular field. I am very new to access and am not sure if I should be using a pivot table, report or query. Can some one put me on the right path please?

 

[Ed Warren]

Barry this is a classic problem in questionaires. Here is a solution

Given a table with a set of questions.
One of the questions say Q333 has the structure you describe, answer one of
several responses, usually a 'Likert Scale" (1-5).

So we have:

QuestionaireID Questionxxxx, Q333, etc

Q333 has as resposes: 1:"Ready to Kill", 2:"Unhappy", 3: "Neutral", 4,
"Happy", 5 "True Bliss"

To get the data you want you need you build a query with fields like

Q333_R1: iif([Q333]=1,1,0) Q333_R2: iif([Q333]=2,1,0) Q333_R3:
iif([Q333]=3,1,0) etc.
(note: iif(test, a,b) returns a if test is true and b if test is false)
(note: if you stored the text responses you would replace the 1 with "Ready
to Kill")

Now when you run the query you have 0 or 1 in each Q333_Rx field and can
total them up to get the number of each response.

Hope this helps.

Ed Warren

I have created a Database in Access 2000, which holds enquiry data taken

from questionnaires filled in by customers. I have a field that is
multiple-choice i.e. where you, V-happy, happy, unhappy or V-unhappy with
the service. I have created the database without any problem but want to
produce a report that shows the percentage of people that where for example
"Happy with the service" I cant seem to get my head round how to do this, I
can create a pivot table that calculates the percentage of people who
answered the question by summing the number of completed fields in the table
and dividing that by the number of records in the database, but how do I
drill down and get percentages of individual answers from a particular
field. I am very new to access and am not sure if I should be using a pivot
table, report or query. Can some one put me on the right path please?

 

[Barry Davidson]

Excelent, thanks Ed, I would never have figured that out on my own, much appreciated
So its basically a query that tests true or false for each condition and then writes the results into another table which I can then total up and calculate my percentages from. Got it, many thanks
Barry.

 

[Ed Warren]

Well sort of, actually you use the data in the table you have, you just
build a query to display it in the format you want. That is the beauty of
databases. Don't mess with the data, just show it the manner you want.

Cheers,

Ed Warren.

Excelent, thanks Ed, I would never have figured that out on my own, much appreciated.
So its basically a query that tests true or false for each condition and

then writes the results into another table which I can then total up and
calculate my percentages from. Got it, many thanks.