0

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.

  • Can you try changing that to ON (MAXTABLE.SEQMAX = Table1.Seq AND MAXTABLE.Product = Table1.Product) ; – ypercubeᵀᴹ Aug 11 '14 at 12:59
  • 2
    Why have you got parantheses opening before Table2 and closing after MAXTABLE.Product? – Mark Sinkinson Aug 11 '14 at 13:09
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.