LEFT JOIN SQL Join Expression not supported

Question

I can't find what is wrong with this SQL expression:

SELECT DISTINCT PNLTERM.TAGSTRIP, TERMS.TERM, PNLTERM.STRIPSEQ, TERMS.WIRENO, 
TERMS.REF, TERMS.MFG, TERMS.CAT, TERMS.BLOCK, PNLTERM.HDL, TERMS.WDBLKNAM, 
TERMS.JUMPER_ID, WIREFRM2.WIRELAY
FROM (PNLTERM LEFT JOIN TERMS ON PNLTERM.LINKTERM = TERMS.LINKTERM) 
INNER JOIN WIREFRM2 ON TERMS.HDL = WIREFRM2.COMP_HDL;

It is something to do with the left join on PNLTERM/TERMS; as it works with a inner and right join but not with a left join. The error is 'Join expression not supported'.

Any ideas?

Answer 1

MS Access has some rules regarding its use of LEFT/RIGHT joins with the INNER joins. Specifically, there is a rule regarding its mix. It is explained here:

A LEFT JOIN or a RIGHT JOIN may be nested inside an INNER JOIN, but an INNER JOIN may not be nested inside a LEFT JOIN or a RIGHT JOIN.

You have a couple of options: You can convert the second join to a left join:

SELECT DISTINCT PNLTERM.TAGSTRIP, TERMS.TERM, PNLTERM.STRIPSEQ, TERMS.WIRENO, 
TERMS.REF, TERMS.MFG, TERMS.CAT, TERMS.BLOCK, PNLTERM.HDL, TERMS.WDBLKNAM, 
TERMS.JUMPER_ID, WIREFRM2.WIRELAY
FROM (PNLTERM LEFT JOIN TERMS ON PNLTERM.LINKTERM = TERMS.LINKTERM) 
LEFT JOIN WIREFRM2 ON TERMS.HDL = WIREFRM2.COMP_HDL;

Or, you can change the query, so that the INNER join comes first:

SELECT DISTINCT PNLTERM.TAGSTRIP, TERMS.TERM, PNLTERM.STRIPSEQ, TERMS.WIRENO, 
TERMS.REF, TERMS.MFG, TERMS.CAT, TERMS.BLOCK, PNLTERM.HDL, TERMS.WDBLKNAM, 
TERMS.JUMPER_ID, WIREFRM2.WIRELAY
FROM (WIREFRM2 INNER JOIN TERMS ON TERMS.HDL = WIREFRM2.COMP_HDL) 
RIGHT JOIN PNLTERM ON PNLTERM.LINKTERM = TERMS.LINKTERM;