MS Access Report SUM field question

[debq]

Hello,

I have a report that provides results of a survey. The survey has 10 questions, 6 are answered with a "YES/NO" and 4 are Based on a Scale: 5=Completely Satisfactory, 4=Mostly Satisfactory ect... I am able to calculate to total number of "yes" answers using =SUM(IIf([Promptness]="Yes",1,0)). My problem occurs when I try to calculate the number of answers that are "5=Completely Satisfactory". I think that using the "=" as a result in my table is causing this issue. How can I sum the total number of answers that are "5=Completely Satisfactory and "4=Mostly Satisfactory in one total per question on my survey?

 

[JBinQLD]

Hi mate,

=SUM(IIf(val([ScaledAnswer]) between 4 and 5, 1,0))

will give you the number of answers that are 4 or 5. If you actually want to add up the values of the 4's and 5's you can of course adjust:

=SUM(IIf(val([ScaledAnswer]) between 4 and 5, val([ScaledAnswer]),0))

Cheers,

JB

 

[dhookom]

I would have structured the table differently (normalized). My solution would be to create a union query so that each answer to each question created a record. Then you can use a simple group by query that counts by Question and Answer.

Duane
Hook'D on Access
MS Access MVP

 

[debq]

YEAH!! Thank you JB, your solution works.

AND yes,Duane, I have learned a very valuable lesson about table creation and will not make the same mistake on future projects.

Thank you both for your help and patience. I still have much to learn but am so excited by Access and by this forum.
debq