MS Access 2016, joining multiple tables with composite keys

Good Morning all,

My first question, so pardon some bad form if it exists.

I currently have three tables (table1, table2, table3), all of which have a composite primary key (model-number, serial-number). The intent there was to split data pertaining to the serial number between parties, where some data was more valuable than others depending on who was looking at it. The composite primary keys share a one-to-one relationship, with keys in t1 related to keys in t2 and keys in t2 related to keys in t3.

I’m trying to write a query that pulls data from all three tables, by serial number. I’m under the impression the best way to do this is with an INNER JOIN. The code I have so far is…

SELECT
    t1.[model-number],
    t1.[serial-number],
    t1.[track],
    t1.[data], --placeholder for data columns
    t2.[data], --placeholder for data columns
    t3.[data] --placeholder for data columns
FROM [table1] t1
LEFT JOIN [table2] t2
    ON t2.[model-number] = t1.[model-number] AND t2.[serial-number] = t1.[serial-number]
LEFT JOIN [table3] t3
    ON t3.[model-number] = t2.[model-number] AND t3.[serial-number] = t2.[serial-number]
WHERE t1.[track] = TRUE
ORDER BY t1.[model-number], t1.[serial-number] DESC;

However, when I run the query, MS Access gives me the error:

Syntax error (missing operator) in query expression “”.

What I’ve tried so far:

Reverse order of t1.[model-number] = t2.[model-number] etc…
Adding/Removing square brackets around t1/t2/t3 alias
Removing the WHERE statement entirely
Putting “AS” in front of table alias callouts (i.e. LEFT JOIN [table1] AS t1)
Cutting down on all the data so that only one field from each table is being referenced (in case I missed an obvious syntax error somewhere in the 30 columns I called out in the original attempt)

Also, after I dismiss the error, the text cursor goes back to ” t2.[“, in second half of the first LEFT JOIN.

All that to say, I can’t find the syntax error. Am I missing something obvious?

Thanks in advance!! The forum has really helped me so far in building this database.

Per comments below, I removed the second LEFT JOIN, so the code read this way:

SELECT
    t1.[model-number],
    t1.[serial-number],
    t1.[track],
    t1.[data], --placeholder for data columns
    t2.[data], --placeholder for data columns
FROM [table1] t1
LEFT JOIN [table2] t2
    ON t2.[model-number] = t1.[model-number] AND t2.[serial-number] = t1.[serial-number]
WHERE t1.[track] = TRUE
ORDER BY t1.[model-number], t1.[serial-number] DESC;

This caused the query to run, so something is happening between the two left joins. I added the second LEFT JOIN back in….

SELECT
    t1.[model-number],
    t1.[serial-number],
    t1.[track],
    t1.[data], --placeholder for data columns
    t2.[data], --placeholder for data columns
    t3.[data]
FROM [table1] t1
LEFT JOIN [table2] t2
    ON t2.[model-number] = t1.[model-number] AND t2.[serial-number] = t1.[serial-number]
LEFT JOIN [table3] t3
    ON t3.[model-number] = t2.[model-number] AND t3.[serial-number] = t2.[serial-number]
WHERE t1.[track] = TRUE
ORDER BY t1.[model-number], t1.[serial-number] DESC;

….with one column from that table in the SELECT statement and it gave me that error. Is there syntax I’m missing between consecutive JOIN clauses?

Answer

Access apparently requires the JOINs to be … I don’t know what the word is ( mutually exclusive perhaps ):

SELECT
    t1.[model-number],
    t1.[serial-number],
    t1.[track],
    t1.[data],
    t2.[data],
    t3.[data]
FROM (
      [table1] t1
         LEFT JOIN [table2] t2
           ON (t2.[model-number] = t1.[model-number]) 
          AND (t2.[serial-number] = t1.[serial-number])
     )
     LEFT JOIN [table3] t3
       ON (t3.[model-number] = t2.[model-number]) 
      AND (t3.[serial-number] = t2.[serial-number])
WHERE t1.[track] = TRUE
ORDER BY t1.[model-number], t1.[serial-number] DESC;

Notice the extra parentheses around the join between t1 and t2? Seems necessary.

Note – The way I discovered this was to use the query design window joining the tables on their composite keys and changing the properties to LEFT (i.e. all rows from the left table and only matching rows from the right table) and then looking at the generated SQL.

Advertisement

Answer