query % change using IIF first time 1

[qwertyth]

Hello,
I have a table with two fields(text) X, Y, both have 20 entries of yes, no.

I am trying to find the percent change in them and am running into trouble if Sum(X='yes')=0 or Sum(Y='yes')=0 or Sum(X='yes')/Sum(Y='yes')=1.

I need

IF Sum(X)=0 and Sum(Y)=0 then %change=0
IF Sum(X)<>0 and Sum(Y)=0 then %change=Sum(X)*100
IF Sum(X)=0 and Sum(Y)<>0 then %change=Sum(Y)*100
IF Sum(X)<>0 and Sum(Y)<>0 then %change=Sum(Y)/Sum(X)
IF Sum(Y)/Sum(X)=1 then %change=0

I used the IIF function for the first time, it seems to work?, dose this look right? , is there a better way to go about this.

SELECT IIF( Sum(Abs(Y ='Yes'))<>0 and Sum(Abs(X = 'Yes'))<>0 AND Sum(Abs(Y ='Yes'))/Sum(Abs(X ='Yes'))<>1 , Sum(Abs(Y ='Yes'))/Sum(Abs(X ='Yes')),IIF( Sum(Abs(Y ='Yes'))/Sum(Abs(X ='Yes'))=1,0,Sum(Abs(Y ='Yes')) + Sum(Abs(Y ='Yes'))) AS [% Change]
FROM tblFlash4;

Thanks for any insight.

 

[qwertyth]

Since I just want the %change regardles of Sum(Abs(Y ='Yes'))>Sum(Abs(X ='Yes'))or Sum(Abs(Y ='Yes'))<Sum(Abs(X ='Yes')) the above needs to be

SELECT IIF( Sum(Abs(Y ='Yes'))<>0 and Sum(Abs(X = 'Yes'))<>0 AND Sum(Abs(Y ='Yes'))/Sum(Abs(X ='Yes'))<>1 ,IIF(Sum(Abs(Y='Yes'))>Sum(Abs(X='Yes')),  Sum(Abs(Y='Yes'))/Sum(Abs(X='Yes'),  Sum(Abs(X='Yes'))/Sum(Abs(Y='Yes'))), IIF( Sum(Abs(Y ='Yes'))/Sum(Abs(X ='Yes'))=1,0,Sum(Abs(Y ='Yes')) + Sum(Abs(Y ='Yes'))) AS [% Change]
FROM tblFlash4;

 

[SkipVought]

Hi,

Not knowing exactly what this table represents, I have some doubts. Let me pose a different kind of example and then you tell me where your data is of a different kind.

Yesterday I HAD $100.
Today I HAVE $75.
% Change = ([Yesterday] - [Today]) / [Yesterday] = (100-75)/100 = -25




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[qwertyth]

Thank you Skip that helped clarify my thinking, my head was spinning around when I wrote the above.

I finality ended up with.

SELECT IIf(Sum(Abs(PM29='Yes'))<>0 And Sum(Abs(PM29='Yes'))/Sum(Abs(PM19='Yes'))<>0,Sum(Abs(PM29='Yes'))/Sum(Abs(PM19='Yes'))-1,Sum(Abs(PM29='Yes'))-Sum(Abs(PM19='Yes'))) AS [% Change]
FROM tblFlash4;
/Code]