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