Using Aliases with two Joins on MS-Access Query

I am attempting to add Joins that will allow the query to display the corresponding tbl_ICA.[Abbreviation] for both tbl_AssyMoves.[To ICA] and tbl_AssyMoves.[From ICA] in each record.

(tables are related as shown here)

I can get a single Join to work fine, but when I add the second I get

Syntax error (missing operator) in query expression ‘t1.[ICA #]=t.[From ICA] INNER JOIN t1 As t2 ON t2.[ICA #]=t.[To ICA’

It seems like it isn’t recognizing the second JOIN as another function. I need the aliases as I’m selecting two different instances of .[Abbreviation].

Query:

SELECT t.[Assy ID], t1.[Abbreviation], t.[From ICA], t2.[Abbreviation], t.[To ICA], t.[To Loc], t.[Date], t.Comments
FROM tbl_AssyMoves As t
INNER JOIN tbl_ICAs As t1
     ON t1.[ICA #]=t.[From ICA]
INNER JOIN t1 As t2
     ON t2.[ICA #]=t.[To ICA]
WHERE ((t.Date Between [Forms]![Form1]![StartDate] And [Forms]![Form1]![EndDate])
**note, additional WHERE criteria omitted for brevity**

Answer

Access syntax is different than other dbms when it comes to joins (among other things).
Requires parentheses:

SELECT t.[Assy ID], t1.[Abbreviation], t.[From ICA], t2.[Abbreviation], t.[To ICA], t.[To Loc], t.[Date], t.Comments
FROM (tbl_AssyMoves As t INNER JOIN tbl_ICAs As t1 ON t1.[ICA #]=t.[From ICA])
INNER JOIN t1 As t2 ON t2.[ICA #]=t.[To ICA]
WHERE ((t.Date Between [Forms]![Form1]![StartDate] And [Forms]![Form1]![EndDate])

Advertisement

Answer