# problem with the Sum(IIf()) function

• ### Question

• Hello,

I have this select statement in my access query:

SELECT NOVI.GBR, NOVI.AB, Min(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]) And ([NALOG1].[POCKM]>0),[NALOG1.POCKM],Null)) AS pockm, Max(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]),[NALOG1.KRAJKM],Null)) AS krajkm, (Max(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]),[NALOG1.KRAJKM],Null))-Min(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]) And ([NALOG1].[POCKM]>0),[NALOG1.POCKM],Null))) AS RAZLIKA, Sum(IIf(([MAGACIN.SIFRA]="0991000") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]),[MAGACIN.KOL],0)) AS Gorivo, Sum(NALOG1.KM) AS SumOfKM, Avg(IIf(([MAGACIN.SIFRA]="0991000" And [NALOG1.KM]<>0),[MAGACIN.KOL],Null)/[NALOG1.KM])*100 AS potrosgor100km, Sum(IIf(([MAGACIN.SIFRA]="0993050" Or [MAGACIN.SIFRA]="0993051"),[MAGACIN.KOL],Null)) AS Motmaslo, Sum(IIf(([MAGACIN.SIFRA]="0992201"),[MAGACIN.KOL],Null)) AS Addblue, ((Sum(IIf(([MAGACIN.SIFRA]="0992201"),([MAGACIN.KOL]),Null)))/(Sum(IIf(([MAGACIN.SIFRA]="0991000"),[MAGACIN.KOL],Null))))*100 AS Addbluegor, Sum(IIf(([MAGACIN.SIFRA]="0999001"),[MAGACIN.KOL],Null)) AS Antifriz, NOVI.DATAP, NOVI.DATAS, IIf(([NOVI].[KM]<=([NOVI].[KMS1]+[NOVI].[KMS2])),"ZA SERVIS",(IIf(([NOVI].[KM]<=([NOVI].[KMP1]+[NOVI].[KMP2])),"PROVERKA",""))) AS Zabeleska
FROM (NALOG1 INNER JOIN NOVI ON NALOG1.GBRV = NOVI.GBR) INNER JOIN MAGACIN ON NOVI.GBR = MAGACIN.GBR
GROUP BY NOVI.GBR, NOVI.AB, NOVI.DATAP, NOVI.DATAS, IIf(([NOVI].[KM]<=([NOVI].[KMS1]+[NOVI].[KMS2])),"ZA SERVIS",(IIf(([NOVI].[KM]<=([NOVI].[KMP1]+[NOVI].[KMP2])),"PROVERKA","")))
HAVING (((NOVI.GBR)>="1002" And (NOVI.GBR)<="1080") AND ((NOVI.AB)="AK"));

but I don't get correct value for Gorivo.

I want to get sum of Gorivo for the certain period, Magacin.Sifra='0991000' and gbr>='1002' and gbr<='1080'

Thanks in avdance.

• Edited by Friday, November 25, 2011 2:07 PM
Friday, November 25, 2011 2:07 PM

### All replies

• Hello
I have select statement with sum(IIF()) function. Here is the biggining part of the result of the select:

untitled1.jpg

And here is the select:
SELECT NOVI.GBR, NOVI.AB, Min(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]) And ([NALOG1].[POCKM]>0),[NALOG1.POCKM],Null)) AS pockm, Max(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]),[NALOG1.KRAJKM],Null)) AS krajkm, (Max(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]),[NALOG1.KRAJKM],Null))-Min(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]) And ([NALOG1].[POCKM]>0),[NALOG1.POCKM],Null))) AS RAZLIKA, Sum(IIf(([MAGACIN.SIFRA]="0991000") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]),[MAGACIN.KOL],0)) AS Gorivo, Sum(NALOG1.KM) AS SumOfKM, Avg(IIf(([MAGACIN.SIFRA]="0991000" And [NALOG1.KM]<>0),[MAGACIN.KOL],Null)/[NALOG1.KM])*100 AS potrosgor100km, Sum(IIf(([MAGACIN.SIFRA]="0993050" Or [MAGACIN.SIFRA]="0993051") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]),[MAGACIN.KOL],Null)) AS Motmaslo, Sum(IIf(([MAGACIN.SIFRA]="0992201") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]),[MAGACIN.KOL],Null)) AS Addblue, ((Sum(IIf(([MAGACIN.SIFRA]="0992201") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]),[MAGACIN.KOL],Null)))/(Sum(IIf(([MAGACIN.SIFRA]="0991000") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]),[MAGACIN.KOL],Null))))*100 AS Addbluegor, Sum(IIf(([MAGACIN.SIFRA]="0999001") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]),[MAGACIN.KOL],Null)) AS Antifriz, NOVI.DATAP, NOVI.DATAS, IIf(([NOVI].[KM]<=([NOVI].[KMS1]+[NOVI].[KMS2])),"ZA SERVIS",(IIf(([NOVI].[KM]<=([NOVI].[KMP1]+[NOVI].[KMP2])),"PROVERKA",""))) AS Zabeleska
FROM (NALOG1 INNER JOIN NOVI ON NALOG1.GBRV = NOVI.GBR) INNER JOIN MAGACIN ON NOVI.GBR = MAGACIN.GBR
GROUP BY NOVI.GBR, NOVI.AB, NOVI.DATAP, NOVI.DATAS, IIf(([NOVI].[KM]<=([NOVI].[KMS1]+[NOVI].[KMS2])),"ZA SERVIS",(IIf(([NOVI].[KM]<=([NOVI].[KMP1]+[NOVI].[KMP2])),"PROVERKA","")))
HAVING (((NOVI.GBR)>="1002" And (NOVI.GBR)<="1080") AND ((NOVI.AB)="AK"));

And here is the table MAGACIN only with values for gbr="1002" date between 01.10.2011 and 31.10.2011, and sifra="091000".

So, the sum of Gorivo, and all the columns where I have sum are not correct. Can anybody help me what I am doing wrong?

Thanks

Sunday, November 27, 2011 6:36 PM
• Hi pet06,

I think you can get more help in T-SQL Forum.

Best Regards

Allen Li [MSFT]
MSDN Community Support | Feedback to us
Monday, November 28, 2011 6:53 AM
• Probably the Access for Developers forum since it's Jet SQL: