none
How to combine several selects RRS feed

  • Question

  • Hello,
     
    I have three tables in my datanase. I also have several slecets which separately return correct values.
    Now I have  a problem to combine them in one query in access. Here are my selects, and my try to combine them, but it is not ok.
    SELECT NOVI.GBR, NOVI.AB,NOVI.DATAP,NOVI.DATAS, [pockm], [krajkm], [RAZLIKA],[SumOfKM],[Gorivo],[potrosgor100km], [Motmaslo], [Addblue], [Addbluegor],[Antifriz], [Zabeleska]
     
    FROM
     
     
     
    (Select NOVI.GBR, IIf( ([NOVI].[KM]<=( ([NOVI].[KMS1]+[NOVI].[KMS2])-1000) ),"ZA SERVIS",(IIf( (([NOVI].[KM]<=([NOVI].[KMP1]+[NOVI].[KMP2]) - 1000),"PROVERKA","") ) ) AS Zabeleska FROM NOVI)as NOVI
     
     
     
    inner join
     
    ( SELECT NALOG1.GBRV, Min(IIf(([NALOG1.DATA]>=[@data1])
     
    And ([NALOG1.DATA]<=[@data2]) And ([NALOG1].[POCKM]>0) And ([NALOG1.GBRV] Between '1001' And '1080'),[NALOG1.POCKM],Null)) AS pockm,
     
    Max(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]) And ([NALOG1.GBRV] Between '1001' And '1080'),[NALOG1.KRAJKM],Null)) AS krajkm,
     
    (Max(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]) And ([NALOG1.GBRV] Between '1001' And '1080'),[NALOG1.KRAJKM],Null))-Min(IIf(([NALOG1.DATA]>=[@data1]) And ([NALOG1.DATA]<=[@data2]) And ([NALOG1].[POCKM]>0),[NALOG1.POCKM],Null))) AS RAZLIKA,
     
     
     
    Sum(NALOG1.KM) AS SumOfKM
     
    FROM NALOG1
     
    GROUP BY NALOG1.GBRV
     
    )as NALOG1 ON NALOG1.GBRV = NOVI.GBR
     
     
     
    INNER JOIN
     
     
     
    (SELECT MAGACIN.GBR, Sum(MAGACIN.KOL) AS Gorivo
     
    FROM MAGACIN
     
    WHERE (((MAGACIN.DATA) Between #10/1/2011# And #10/31/2011#)) and
     
    ((MAGACIN.GBR) between '1001' and '1080') and ((MAGACIN.SIFRA)='0991000')
     
    GROUP BY MAGACIN.GBR, MAGACIN.SIFRA) as MAGACIN on NALOG1.GBRV=MAGACIN.GBR
     
    inner join
     
    (SELECT MAGACIN.GBR, Sum(MAGACIN.KOL) AS Motmaslo
     
    FROM MAGACIN
     
    WHERE (((MAGACIN.DATA) Between #10/1/2011# And #10/31/2011#)) and
     
    ((MAGACIN.GBR) between '1001' and '1080') and (([MAGACIN.SIFRA]="0993050" Or [MAGACIN.SIFRA]="0993051"))
     
    GROUP BY MAGACIN.GBR, MAGACIN.SIFRA)as MAGACIN on NALOG1.GBRV=MAGACIN.GBR
     
    inner join
     
    (SELECT MAGACIN.GBR, Sum(MAGACIN.KOL) AS Addblue
     
    FROM MAGACIN
     
    WHERE (((MAGACIN.DATA) Between #10/1/2011# And #10/31/2011#)) and
     
    ((MAGACIN.GBR) between '1001' and '1080') and (([MAGACIN.SIFRA]="0992201"))
     
    GROUP BY MAGACIN.GBR, MAGACIN.SIFRA)as MAGACIN on NALOG1.GBRV=MAGACIN.GBR
     
    inner join
     
     
     
    ( SELECT MAGACIN.GBR, ((Sum(IIf(([MAGACIN.SIFRA]="0992201") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]) And ([MAGACIN.GBR] Between '1001' And '1080'),[MAGACIN.KOL],Null))/(Sum(IIf(([MAGACIN.SIFRA]="0991000") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]),[MAGACIN.KOL],Null))))*100) AS Addbluegor
     
    FROM MAGACIN
     
    GROUP BY MAGACIN.GBR
     
    )as MAGACIN on NALOG1.GBRV=MAGACIN.GBR
     
    inner join
     
    (SELECT MAGACIN.GBR, Sum(MAGACIN.KOL) AS Antifriz
     
    FROM MAGACIN
     
    WHERE (((MAGACIN.DATA) Between #10/1/2011# And #10/31/2011#)) and
     
    ((MAGACIN.GBR) between '1001' and '1080') and (([MAGACIN.SIFRA]="0999001"))
     
    GROUP BY MAGACIN.GBR, MAGACIN.SIFRA)as MAGACIN on NALOG1.GBRV=MAGACIN.GBR
     
    inner join
     
     
     
    (SELECT Avg(IIf( ([MAGACIN.SIFRA]="0991000" And [NALOG1.KM]<>0),[MAGACIN.KOL],Null)/([NALOG1.KM])*100) AS potrosgor100km
     
    From MAGACIN, NALOG1) )   ///only this select doesn't function
     
    ON NOVI.GBR = MAGACIN.GBR
    So how to combine all the selects that return fine results?
    Can anybody help me please?
    Thank you in advance.
    Tuesday, November 29, 2011 10:13 AM