Gremmbol Gremmbol - 2 months ago 6
SQL Question

Access/SQL Query JOIN Created Tables

I have following query:

SELECT t1.Material, t1.Amount, t2.amtLast, t3.amtLast
FROM FAUF t1
LEFT JOIN
(SELECT FAUF.Material AS mat, FAUF.Amount AS amtLast
FROM FAUF
WHERE (year=2014)) t2
ON (t1.Material = t2.mat)
LEFT JOIN
(SELECT FAUF.Material AS mat, FAUF.Amount AS amtLast
FROM FAUF
WHERE (year=2013)) t3
ON (t1.Material = t3.mat)
WHERE (t1.year=2015);


MS Access is giving me an error, that a expression is missing in (t1.Material = t2.mat). If I exclude the last JOIN the query works, what am i missing?

Answer

MS Access requires additional parentheses for joins. Try this:

SELECT t1.Material, t1.Amount, t2.amtLast, t3.amtLast
FROM (FAUF t1 LEFT JOIN
      (SELECT FAUF.Material AS mat, FAUF.Amount AS amtLast
       FROM FAUF
       WHERE (year=2014)
      ) t2
     )
     ON (t1.Material = t2.mat) LEFT JOIN
     (SELECT FAUF.Material AS mat, FAUF.Amount AS amtLast
      FROM FAUF
      WHERE (year=2013)
    ) t3
    ON (t1.Material = t3.mat)
WHERE (t1.year = 2015);

However, you can do this with conditional aggregation, which is simpler than the join method:

select f.Material,
       sum(iif(f.year = 2015, amount, 0)) as amt_2015,
       sum(iif(f.year = 2014, amount, 0)) as amt_2014,
       sum(iif(f.year = 2013, amount, 0)) as amt_2013
from fauf f
group by f.Material;
Comments