percent problem 1

[LloydDobler]

I'm trying to get a simple percentage but they keep adding instead of becoming a percentage.

select contract, sum(IIF(subqry.ACCEPTED + subqry.DECLINED=0,0, subqry.ACCEPTED / (subqry.ACCEPTED + subqry.DECLINED))) as RATE
from subqry
where (((DatePart("yyyy",[referraldate]))=DatePart("yyyy",Now())))
GROUP BY contract

The results I get for the above query are 300% instead of 100, 167% instead of 67%. In once case, I have 7 Accepted into 8 total and I get 700. What am I doing wrong?


"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[genomon]

Your SUM function is adding everything between the first and last parens.

Cogito eggo sum – I think, therefore I am a waffle.

 

[dhookom]

select contract, IIF(sum(ACCEPTED + DECLINED)=0,0, Sum(ACCEPTED) / Sum(ACCEPTED + DECLINED)) as RATE
from subqry
where Year([referraldate])=Year(Date())
GROUP BY contract;

Duane
Hook'D on Access
MS Access MVP

 

[LloydDobler]

Thanks Duane & Genomon. Had a feeling that was going to be the issue but every time I tried SUM elsewhere I kept getting syntax errors.

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."