none
problem with the Sum(IIf()) function RRS feed

  • 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'

    Can anybody help me please?

    Thanks in avdance.


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

Answers

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
    Moderator
  • Probably the Access for Developers forum since it's Jet SQL:

    http://social.msdn.microsoft.com/Forums/en-US/accessdev/threads

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, November 28, 2011 4:16 PM