Counting Results

[Eileen]

I tried to save some steps in my db by using the same lookup table for a number of questions. In my report, I'm trying to count the results for these questions, but as I try to use the formula ex. =Sum(IIF([Responses]=1,1,0)) to count the results for each question, I' m getting the same results for each question.

I'm trying to avoid making any more lookup tables. Does anyone know if I can get around this? I'm trying to reference the field to the formula as =Sum(IIF([Responses.FieldName]=1,1,0)) .. not going anywhere.

Thanks.


Eileen

 

[Russell Hauf]

Can you give more information? Like what your data looks like and the results you are getting now....

Thank you,

Russell

 

[Ziggy]

Don't you need to use the "Count" function first and then Sum.

If it makes it easier set up a expression using "Count" in the Query bound to your report (if that's how it's set up), and then just Sum the expression in the report.

Ziggy

 

[Eileen]

Report Calculations

I figured out what my problem was. I used the same lookup table for several questions, but it's not the lookup table that goes in my formula, it's the field name.

Ex: Not Right =Sum(IIF([Response]=1,1,0)) is the lookup table
Ex: Right =Sum(IIF([Field Name]=1,1,0)) is the field name

I tested this and it worked. The one problem I can't figure out yet is if one of my fields has strings, ex. atlantic, mercer, middlesex ... how can I sort the same responses together and get a count out of it? Ex. atlantic 2, mercer 5, middlesex 8. The wizard doesn't work
because I have too many fields and I unfortunately have to do it manually.

Eileen