I am querying a couple tables, and for some reason Microsoft Access 2016 continually informed me that my SQL query JOIN ON syntax is incorrect. Specifically, the JOIN syntax is highlighted, followed by the ON syntax when removing that (the latter for clearer reasons based on prior knowledge of SQL JOIN ON syntax).

Here is the code:

SELECT Column1, Column2, Table1.Column3
FROM Table1 
JOIN Table2
    ON Table1.Column3 = Table2.Column3
   AND Column4 = '{NAME}'
ORDER BY Column4;

Is there something incorrect about this syntax? By the way, this is using ANSI-89 Syntax.


3 Answers 3


Access requires INNER JOIN. The simple JOIN (which should default to INNER JOIN according to the SQL standard) is not allowed in Access.


The solution I realized needed the INNER JOIN syntax rather than JOIN ON syntax for Access 2016 as JOIN ON without a combination of INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, etc. does not work with this DBMS (Database Management System). Further, I changed the AND logical operator to a WHERE clause as JOIN ON syntax followed simply by AND does not run in Access 2016. Furthermore, the GROUP BY syntax goes beyond the minimum requirements for this query, so I removed it. Therefore, the solution to my question is as follows:

SELECT Column1, Column2, Table1.Column3

FROM Table1 


ON Table1.Column3 = Table2.Column3

WHERE Column4 = '{NAME}';
  • I think you are wrong about the AND part. I think Access allows ON (condition AND condition2)`. It may need parentheses though. Commented Sep 5, 2016 at 17:49
  • Your proposal is syntactically different than mine was because the code after the ON of the INNER JOIN syntax would be considered a single statement. Your code suggestion works equivalently, but the AND in my original code was after the INNER JOIN ON syntax. AND being a logical operator works between two data entries. Therefore, as I understand it, saying AND at the beginning of a SQL statement is syntactically incorrect. Hence, my original code would not run even with INNER JOIN
    – JMK
    Commented Sep 6, 2016 at 20:20
  • It certainly would not run if you didn't try it. If you tried it, it would run ;) Commented Oct 8, 2016 at 21:11
  • I think that is missing the point of my response in that your suggestion did not apply to the context of my requirements. Whether it worked is not as important as its relevance to the SQL requirement at that time.
    – JMK
    Commented Oct 8, 2016 at 22:08

After this passage ON Table1.Column3 = Table2.Column3 you should put where instead of and.

  • You are correct. I did that as well. Having said that, the JOIN ON syntax does not run without the INNER syntax
    – JMK
    Commented Sep 2, 2016 at 20:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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