Percentage Calculation in Database

[Guest]

I have compiled data from a survey.

I have a simple data table with the following fields:
SchoolName
AdvisorLastName
SSN -- Soc. Sec. #
Question -- the Question number (e.g. Q1, Q2, ...)
Answer -- any number, one through five.

I would like to create a report, organized by Question, that calculates the
percentage of positive responses, i.e. any answers > 3, for the Advisor.

Any assistance would be greatly appreciated!

Thank you.

 

[John Vinson]

I have compiled data from a survey.

I have a simple data table with the following fields:
SchoolName
AdvisorLastName
SSN -- Soc. Sec. #
Question -- the Question number (e.g. Q1, Q2, ...)
Answer -- any number, one through five.

I would like to create a report, organized by Question, that calculates the
percentage of positive responses, i.e. any answers > 3, for the Advisor.

Any assistance would be greatly appreciated!

Thank you.

Create a Query based on your table. Change it to a Totals query by
clicking the Greek Sigma icon.

Put a calculated field in a vacant Field cell by typing:

PosResp: IIF([Answer] > 3, 100, 0)

Select the SchoolName and AdvisorLastName fields (or just the
AdvisorLastName, if you want to average across schools) and use "Group
By" on its Total row; and use "Average" on the total row under
PosResp.

Nitpicky question: what if you have two advisors named Brown (Fred and
Lona say)? or for that matter two advisors named Jim Smith?

John W. Vinson[MVP]

 

[Guest]

Thank you for your prompt, helpful response.

As for the possibility of duplicate names for the advisors, I will come up
with a normalized table with each advisor with a auto-number as a primary
key. I am far better at normalizing data and using basic queries than
developing queries to base reports on.

As for the percentage, I have an additional question: how do I actually
compute the percentage?

That is, I need to calculate the number of positive responses (any response
that is > 3) out of the total responses.

Once I have the percentage, I think I will be able to create a report
organized by advisor and question number.

But, unfortunately, I am using three queries to calculate the percentage: 2
count queries and a third query based on the other two.

I have over 50 questions, about 60 advisors, 9 colleges, about 2000
responses, and (at least) 2 different ways that they want to report the data.
Obviously, I want to make this as elegant and simple as possible.

The current method uses Excel spreadsheets and lots of data-entry... Please
help!

I have compiled data from a survey.

I have a simple data table with the following fields:
SchoolName
AdvisorLastName
SSN -- Soc. Sec. #
Question -- the Question number (e.g. Q1, Q2, ...)
Answer -- any number, one through five.

I would like to create a report, organized by Question, that calculates the
percentage of positive responses, i.e. any answers > 3, for the Advisor.

Any assistance would be greatly appreciated!

Thank you.

Create a Query based on your table. Change it to a Totals query by
clicking the Greek Sigma icon.

Put a calculated field in a vacant Field cell by typing:

PosResp: IIF([Answer] > 3, 100, 0)

Select the SchoolName and AdvisorLastName fields (or just the
AdvisorLastName, if you want to average across schools) and use "Group
By" on its Total row; and use "Average" on the total row under
PosResp.

Nitpicky question: what if you have two advisors named Brown (Fred and
Lona say)? or for that matter two advisors named Jim Smith?

John W. Vinson[MVP]

 

[John Vinson]

As for the percentage, I have an additional question: how do I actually
compute the percentage?

Use the expression I provided.

It calculates a value of 100 for a positive response, and 0 for a
negative response. The Totals query then averages these values. If the
person has three positive and three negative responses, the query will
average 100, 100, 100, 0, 0, and 0, giving a result of 50 - fifty
percent positive responses.

John W. Vinson[MVP]