Combining selects in one select in access query (1 Viewer)

[nelpet]

Hello,

I have three tables and I use fields from all of them, and aggregate functions to make select for my report. I tried to make separate selects that return separately correct values, but I don't know how to combine them and get one select in the query which I will use for the Crystal Report and will return me correct values for the fields. Here is my try to combine them :

SELECT NOVI.GBR, NOVI.AB, NOVI.DATAP, NOVI.DATAS, [pockm], [krajkm], [RAZLIKA], [SumOfKM], [Gorivo], [Motmaslo], [Addblue], [Addbluegor], [Antifriz]

FROM
(SELECT NOVI.GBR, NOVI.AB, NOVI.DATAP, NOVI.DATAS
FROM NOVI
WHERE (((NOVI.GBR) Between '1001' And '1080') AND (NOVI.AB)='AK') ) N

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) Gr on Gr.GBRV=N.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) G on Gr.GBRV=G.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) Mm on G.GBR=Mm.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) A on A.GBR=Mm.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') and (NOVI.AB)='AK',[MAGACIN.KOL],Null))/(Sum(IIf(([MAGACIN.SIFRA]="0991000") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]) and (NOVI.AB)='AK',[MAGACIN.KOL],Null))))*100) AS Addbluegor
FROM MAGACIN INNER JOIN NOVI ON MAGACIN.GBR=NOVI.GBR
WHERE NOVI.AB='AK'
GROUP BY MAGACIN.GBR) Ag on Ag.GBR=A.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) Af on Af.GBR=Ag.GBR

And this returned me an error:
"Syntax error (missing operator) in query expression 'Gr.GBRV=N.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, "

Can anybody help me please?

 

[Jeanette Cunningham]

The syntax is not correct.
In access, there is a specific way to create a join.
Here is a simple example:

SELECT tblCase.CaseID, tblCase.CaseEEID, Count(tblDiary.DiaryID) AS OpenDiaries
FROM tblCase INNER JOIN tblDiary ON tblCase.CaseID = tblDiary.ClmNum;

I suggest you create a query in the query designer in access using just 2 tables for a start to get an understanding of the correct syntax for queries created in access. Then add the next table. You can usually create the aggregate query in the query designer which will then give you the correct syntax.

 

[nelpet]

Ok, can you help me please how to combine into one query these two which work when tested as standalone selects:

SELECT MAGACIN.GBR, [@data1] AS Expr1, [@data2] AS Expr2, Sum(IIf([MAGACIN.SIFRA]="0992201",MAGACIN.KOL,0)) AS Addblue, Sum(IIf([MAGACIN.SIFRA]="0999001",MAGACIN.KOL,0)) AS Antifriz, Sum(IIf([MAGACIN.SIFRA]="0991000",MAGACIN.KOL,0)) AS Gorivo, Sum(IIf((([MAGACIN.SIFRA])="0993050" Or ([MAGACIN.SIFRA])="0993051"),MAGACIN.KOL,0)) AS Motmaslo, ((Sum(IIf(([MAGACIN.SIFRA]="0992201") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]) And ([MAGACIN.GBR] Between '1001' And '1080') And (NOVI.AB)='AK',[MAGACIN.KOL],Null))/(Sum(IIf(([MAGACIN.SIFRA]="0991000") And ([MAGACIN.DATA]>=[@data1]) And ([MAGACIN.DATA]<=[@data2]) And (NOVI.AB)='AK',[MAGACIN.KOL],Null))))*100) AS Addbluegor
FROM NOVI INNER JOIN MAGACIN ON NOVI.GBR = MAGACIN.GBR
WHERE (((MAGACIN.DATA) Between #10/1/2011# And #10/31/2011#) AND ((MAGACIN.GBR) Between '1001' And '1080') AND (([NOVI.AB])='AK'))
GROUP BY MAGACIN.GBR

and


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
FROM NALOG1 INNER JOIN NOVI ON NALOG1.GBRV = NOVI.GBR
WHERE (((NOVI.AB)='AK'))
GROUP BY NALOG1.GBRV;

Thanks

 

[Galaxiom]

When creating joins between multiple tables each join is nested in an ever increasing number of pairs of parentheses. Basically the subsequent join is between the existing joined group of tables and the next joined table.

The easiest way to see this is in by creating joins between multiple tables in the query designer and observe the way the parentheses are used in the SQL view.

BTW. Use code tags when posting so your code is displayed in a code box. This maintains original formatting and uses a better font for the readability of sequential quote marks.

 

[Jeanette Cunningham]

This same question has been asked in the microsoft office answers forum and has rececived many helpful answers already.
http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/4af7ab27-7b04-4550-9806-c7d7e0bfe658/