count/sum issue

[saschamps9903]

I have a query in which I am using the sum IIf function to count the number
of check boxes in several columns as it relates to a loan account. Now my
count criteria is being changed so that any loan that has 2 check marks
should only be counted once and erasing the additional checkmark is not an
option. Here is an example of what I mean:

Loan Num *******Issue 1*******Issue 2******Issue 3
123456 ******* -1 ******* 0 ****** -1

Instead of using sum IIf to just count each issue, I need to count the issue
only once. Issue 1 would be the most serious issue and the one that needs to
be counted. Is there an alternative to Sum IIf or am I out of luck?

 

[[MVP] S.Clark]

I don't really know what the 'Sum IIF' function is, but I can tell you that
you'd be better off normalizing your tables and scrapping the whole checkbox
concept. Nothing but trouble that is.

 

[saschamps9903]

I am too far into this to turn back now. the sum iif (and I apologize if I
am not correctly identifying) is like this Expr1:
Sum(IIf([somefield]="No",1,0)). does anybody have a suggestion that can help
me out? thanks.

 

[John Spencer (MVP)]

Sum(IIF([SomeField]=False Or [SomeOtherField]=False or [YetAnotherfield]=False,1,0))

That will work, but it really looks like you might need to look at a redesign.

I am too far into this to turn back now. the sum iif (and I apologize if I
am not correctly identifying) is like this Expr1:
Sum(IIf([somefield]="No",1,0)). does anybody have a suggestion that can help
me out? thanks.

I don't really know what the 'Sum IIF' function is, but I can tell you that
you'd be better off normalizing your tables and scrapping the whole checkbox
concept. Nothing but trouble that is.