SQL in Access 2007 - Syntax error

Question

I am using Access 2007 to create an SQL query to join two tables. I was able to do that but then I don't have the rows where the columns from the second table are NULL; I just have the rows where there is information in the second table that matches. I tried to do a LEFT JOIN but Access didn't like this. Instead I am trying to create a better 'join/on' addition to my query (seen below) but now I am getting a "Syntax error in FROM clause". Do you have any ideas what I am doing wrong?

SELECT *
FROM dbo_table1 AS t1
JOIN dbo_table2 AS t2
ON (
     (t1.id = t2.id)   // where the two ids match so the info in table 2 match with table 1
     OR 
     ((SELECT COUNT(*) FROM dbo_table2 AS t3 WHERE t1.id = t3.id)=0)  // there is no match but I want the row with NULL for the values from the second table
   );

Answer 1

If you want all rows from dbo_table1, whether or not there is a matching row in dbo_table2, use a LEFT JOIN. Access should accept this one:

SELECT *
FROM
    dbo_table1 AS t1
    LEFT JOIN dbo_table2 AS t2
    ON t1.id = t2.id;

Answer 2

You can have an outer / cross join with a WHERE statement (a.id=b.id or b.id is null).

Or a UNION, the first being all a.id = b.id, the second where b.id is null.

(Depending on your exact requirements)