HiveBrain v1.2.0
Get Started
← Back to all entries
debugMinor

JOIN ON syntax error in Microsoft Access 2016

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
errormicrosoftjoinsyntaxaccess2016

Problem

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.

Solution

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 

INNER JOIN Table2

ON Table1.Column3 = Table2.Column3

WHERE Column4 = '{NAME}';

Code Snippets

SELECT Column1, Column2, Table1.Column3

FROM Table1 

INNER JOIN Table2

ON Table1.Column3 = Table2.Column3

WHERE Column4 = '{NAME}';

Context

StackExchange Database Administrators Q#148701, answer score: 2

Revisions (0)

No revisions yet.