sum 2 columns

[Guest]

Hi i am using this statement in my query

SUM(Iif (BatRuns >= 100, 1, 0)) AS Hundred

this works fine but i now need to add another column BatRuns2 to this i have tried

SUM(Iif (BatRuns >= 100, 1, 0)(BatRuns2 >= 100, 1, 0)) AS Hundred

just not sure how to do it
can someone help
thanks

[Dan_Bracuk]

quote:

---

Originally posted by: JohnGree
Hi i am using this statement in my query

SUM(Iif (BatRuns >= 100, 1, 0)) AS Hundred

this works fine but i now need to add another column BatRuns2 to this i have tried

SUM(Iif (BatRuns >= 100, 1, 0)(BatRuns2 >= 100, 1, 0)) AS Hundred

just not sure how to do it
can someone help
thanks

---

I don't know the answer, but, usually when you want to add two numbers together you put a plus sign between them. If that is not what you are attempting, your question is too vague.

[Guest]

ok sorry what i need to do is count how many time 100 or more appears in columns

batruns
and
batruns2

[Dan_Bracuk]

Does you db support this syntax?
select case
when this then that
when these and those then the_other_one
else something_else
end as something

If so, it would be a better approach.

[Guest]

i am using microsoft access, not sure if this code would work

[MikerRoo]

For EITHER column having >= 100:
SUM (Iif ( (BatRuns >= 100) OR (BatRuns2 >= 100), 1, 0) ) AS Hundred

For BOTH columns having >=100 simultaneously:
SUM (Iif ( (BatRuns >= 100) AND (BatRuns2 >= 100), 1, 0) ) AS Hundred

Edit: fixed parenthesis

[MikerRoo]

For SUM of columns >= 100:
SUM (Iif ( (BatRuns + BatRuns2 ) >= 100, 1, 0) ) AS Hundred



Edit: fixed parenthesis

[Guest]

Thanks for that i have used
SUM (Iif ( (BatRuns >= 100) OR (BatRuns2 >= 100), 1, 0) ) AS Hundred

but it only ads the BatRuns2 column, not sure why this is

i just need to count the number of times 100 or more appears in these columns, the code seams logical to me but doesnt work

any ideas