Microsoft Access – JOIN on Derived Table Not Working

ms accessms-access-2010

Can you please help me why my below SQL is not working in Access 2010?

SELECT
  Table2.Product, 
  Table2.Color, 
  SEQMAX,
  Table1.Attribute
FROM 
( 
  Table2

  LEFT JOIN
  (
    SELECT
      Table1.Product, 
      MAX (Table1.Seq) as SEQMAX
    FROM Table1
    GROUP BY Table1.Product
  ) AS MAXTABLE   
  ON  Table2.Product = MAXTABLE.Product
)
LEFT JOIN  Table1
  ON MAXTABLE.SEQMAX = Table1.Seq 
  AND Table2.Product = Table1.Product ;

It is fine until I add the last line AND Table2.Product = Table1.Product both pieces of the AND worked fine separately, however I can't figure out why they don't work when combined.

I'm getting an error: JOIN expression not supported.

Best Answer

Perhaps rewrite the statement so Microsoft Access can more easily understand the requirements.

Something like:

SELECT
  T1.Product, 
  T1.Color, 
  T1.SEQMAX,
  Table1.Attribute
FROM (
    SELECT Table2.Product   
        , Table2.Color
        , SEQMAX
    FROM Table2
        LEFT JOIN
        (
            SELECT
              Table1.Product, 
              MAX (Table1.Seq) as SEQMAX
            FROM Table1
            GROUP BY Table1.Product
        ) AS MAXTABLE ON  Table2.Product = MAXTABLE.Product
    ) AS T1
    LEFT JOIN Table1
      ON T1.SEQMAX = Table1.Seq 
        AND T1.Product = Table1.Product;
Related Question