Create a Total Percentage in the Report Footer

[plaguna]

I’m creating a Microsoft Access Report of 6 different questions with “Yes” and “No” answers. I have no problem to count the Yes and Nos with the following formulas for each question in the Report Footer:
=Count(IIf([Q1]=”YES”,0))
=Count(IIf([Q1]=”NO”,0))
My question is: how can I get a percentage of NOs for each question? I tried to create a new field (Total of Yes) and (Total of No) in my query with the above formulas, that way I can manipulate fields on the Report, but It didn’t work. So, I’m trying to work it out on my Report. The basic formula would be (Count of NOs Divided by Total Yes and NOs) How can I translate this in the Report Footer.
Please, can somebody help me? Thanks in advance.
I’m using MS Access 2007.

Plaguna

[Stewart Ross]

Hi plaguna. If this approach is working for you then the simplest way to do what you ask is to OR the two elements:

=Count(IIf(([Q1]=”YES”) OR ([Q1="NO"),0))

There are other ways to do this that would be more flexibile, in particular by adding calculated fields to the report's underlying query, but as your approach is working there is no good reason to change it at present.

-Stewart

[plaguna]

Ooh dear - I am very sorry plaguna; instead of replying to your question about how to total your field I have overwritten it - I do apologise.

You had asked how to create a percentage in your report representing the number of "No" responses divided by the total responses, now that you had the total itself.

I have split my overwritten reply to your question out from what was your post, which unfortunately I had removed from my reply. Sorry for this very silly mistake.

-Stewart

[Stewart Ross]

Hi plaguna. To get the percentage you need to use a similar COUNT approach to what you have done already. In your percentage textbox set its control source to

=100 * Count(IIF([Q1]="NO", 0))/Count(IIf(([Q1]=”YES”) OR ([Q1="NO"),0))

This is not a flexible or elegant solution, as it repeats the counting of the underlying data. This is why I mentioned in my previous post that the approach I would use is to put the base data for such calculations into the report's base query instead, where the counts etc are done just once.

Although what is listed should work, for reporting on questionnaire responses it would be more normal to base your report on a totals query which provides for each question the total number of responses, and a count of the individual responses provided. Basing your report on such a query you could then do percentages and other calculations (averages, minimums and maximums and so on) without repeating the counting of fields over and over again.

Do as much of the work outside of the report as possible, using calculated fields in the base query to set up useful summary data for your report.

-Stewart

[plaguna]

Stewart,
Don’t worry about overwriting my reply. The most important is that I can see that you understand my question.
I tried the code you gave me:

=100 *Count(IIF([Question]="NO", 0))/Count(IIf(([Question]=”YES”) OR ([Question]="NO"),0))

and it didn’t work. I tried this:

= 100 *Count(IIF([Question]="No", 0))/Count(IIf([Question]=”Yes”,1,0))

and it keeps asking me to enter parameter value “Yes” all the time. It gives me the percentage of NOs only after I enter the value (# of Yes). If I could be able to generate the value that I’m looking for automatically, would be great.

I understand your point when you say that the approach you would use is to put the base data into the report's base query instead, and do as much of the work outside of the report as possible. I apologize for my limited level of knowledge but, I don’t know how to get a single value (percentage) in a query when there are more than one answer in a column. I tried different ways, and Iooked for different sources but, I guess I’m missing something. If you can give me a clue or something that I can work on, would be a big help
Thank you again.

Plaguna

[Stewart Ross]

Hi again plaguna. I checked the code again and found one inconsistency which I had not noticed first time round.

In the part which you now have as IIf([Question]=”Yes”,1,0) the double quotes are not plain double quotes but word processor-type closing quotes. These were included when I copied that section of your first post.

The revised version with the corrected type of quotes is:

=100 * Count(IIF([Question]="NO", 0))/Count(IIf(([Question]="YES") OR ([Question]="NO"),0))

For the base query approach there are two stages:

1. instead of referring to the table of answers directly add the table to a query in which you can place calculated fields
2. devise a totals query which summarises the data in the answers query

Without full details of the structure of your tables and the type of question and answer it is difficult to give you a correct way to calculate the percentage without making possibly incorrect assumptions.

The percentage of No answers is just (number of Nos) / (number of answers) * 100, but to be correct you need to be clear about the responses: are answers always just Yes or No? Is there also a Maybe, say? Can a respondent leave the question blank, and if so is this to be left out of the calculation, as I would expect?

Assuming that answers can only be Yes or No it is not necessary to count both Yes and No answers, because the count of No's is just the number of answers less the number of Yes's (and vice versa).

The stage 1 query is like this:

SELECT QNumber, Answer, IIF(Answer="YES", 1, 0) as CountYes FROM Answers;

Assuming this was named qryStage1, the stage2 query which does the percentage is then

SELECT QNumber, Sum(countYes) as TotalCountYes, Count(Answer) - Sum(CountYes) as TotalCountNo, 
Sum(CountYes)/Count([Answer]) * 100 as PercentYes, (1 - (Sum(CountYes)/Count([Answer]))) * 100 as PercentNo
FROM qryStage1
GROUP BY QNumber;

These are ideas for you to try out when you have time to investigate other solutions.

-Stewart

[plaguna]

Stewart,
Thank you very much. It works perfectly. I love it !!!! I tried the two approaches you suggested and both work without any problem. I really appreciate it.

PLaguna