Calculating Percentages Using a Query

[Tom Glatt]

I have a database with consumer responses to a survey question. The response
options are A, B, or C. The response table has a variety of individuals with
their appropriate response to the question (e.g. NAME field and RESPONSE
field)

I want to calculate the percentage of each letter response (for example, the
percentage of A's, the percentage of B's, etc.). This requires that I count
all "A" responses and then divide by total overall responses.

I then want to subtract the percentage of A response from the percentage of
B responses to arrive at a net number.

I have tried a variety of approaches, but none seem to work. I typically use
Excel to calculate the data I am looking for, but was hoping I could create a
report in Access that calculates the same basic information (and eliminate a
step in my data analysis). Clearly I am a novice when it comes to
calculations in a database.

If anyone has an idea on how to approach the issue I would appreciate some
feedback. I don't want someone to create the solution for me - just point me
in the right direction.

Thanks!

 

[Marshall Barton]

I have a database with consumer responses to a survey question. The response
options are A, B, or C. The response table has a variety of individuals with
their appropriate response to the question (e.g. NAME field and RESPONSE
field)

I want to calculate the percentage of each letter response (for example, the
percentage of A's, the percentage of B's, etc.). This requires that I count
all "A" responses and then divide by total overall responses.

I then want to subtract the percentage of A response from the percentage of
B responses to arrive at a net number.

I have tried a variety of approaches, but none seem to work. I typically use
Excel to calculate the data I am looking for, but was hoping I could create a
report in Access that calculates the same basic information (and eliminate a
step in my data analysis). Clearly I am a novice when it comes to
calculations in a database.

Maybe you can get som ideas from:

SELECT Response, Count(*) As TotalPeople,
Count(Response) As TotalResponders,
Count(IIf(Response="A",1,Null)) / Count(Response) As
PercentA,
Count(IIf(Response="B",1,Null)) / Count(Response) As
PercentB
FROM [response table]

 

[Tom Glatt]

I tried your query but for some reason could not get it to work - though it
did send me in the right direction - I think. What I came up with is the
following statement. Though it works, I am wondering if it is bad query
design. Incidentally, I used the appropriate field names rather than a basic
description as in my original post.


SELECT Count(SurveyData.MyNumber) AS TotalResponses,
Sum(IIf([MyNumber]>8,1,0))/ Count(SurveyData.MyNumber) AS TotalPromoters,
Sum(IIf([MyNumber]<7,1,0))/ Count(SurveyData.MyNumber) AS TotalDetractors,
Sum(IIf([MyNumber]>8,1,0))/
Count(SurveyData.MyNumber)-Sum(IIf([MyNumber]<7,1,0))/
Count(SurveyData.MyNumber) AS NetPromoters
FROM SurveyData;

I have a database with consumer responses to a survey question. The response
options are A, B, or C. The response table has a variety of individuals with
their appropriate response to the question (e.g. NAME field and RESPONSE
field)

I want to calculate the percentage of each letter response (for example, the
percentage of A's, the percentage of B's, etc.). This requires that I count
all "A" responses and then divide by total overall responses.

I then want to subtract the percentage of A response from the percentage of
B responses to arrive at a net number.

I have tried a variety of approaches, but none seem to work. I typically use
Excel to calculate the data I am looking for, but was hoping I could create a
report in Access that calculates the same basic information (and eliminate a
step in my data analysis). Clearly I am a novice when it comes to
calculations in a database.

Maybe you can get som ideas from:

SELECT Response, Count(*) As TotalPeople,
Count(Response) As TotalResponders,
Count(IIf(Response="A",1,Null)) / Count(Response) As
PercentA,
Count(IIf(Response="B",1,Null)) / Count(Response) As
PercentB
FROM [response table]

 

[Marshall Barton]

I tried your query but for some reason could not get it to work - though it
did send me in the right direction - I think. What I came up with is the
following statement. Though it works, I am wondering if it is bad query
design. Incidentally, I used the appropriate field names rather than a basic
description as in my original post.


SELECT Count(SurveyData.MyNumber) AS TotalResponses,
Sum(IIf([MyNumber]>8,1,0))/ Count(SurveyData.MyNumber) AS TotalPromoters,
Sum(IIf([MyNumber]<7,1,0))/ Count(SurveyData.MyNumber) AS TotalDetractors,
Sum(IIf([MyNumber]>8,1,0))/
Count(SurveyData.MyNumber)-Sum(IIf([MyNumber]<7,1,0))/
Count(SurveyData.MyNumber) AS NetPromoters
FROM SurveyData;

Looks good to me.

Note the difference between using Count(*) and
Count(MyNumber). Count(*) tells you the number of records
in the query, while Count(MyNumber) counts the number of non
Null entries in the MyNumber field. Count(*) is usually a
lot faster so it it preferred when you can guarantee that
the MyNumber field has no non Null entried.

I noticed that you changed from:
Count(IIf(MyNumber>8,1,Null))
to:
Sum(IIf(MyNumber>8,1,0))
That is fine. I just wanted to emphasize that both of those,
along with a gazillion other variations, all produce the
same result.