Full outer join in Access

[johnseito]

Hello,



Is there an easier way to do full outer join in Access compare to SQL server.

In Access we need to to left join then right join where left table is null then union them.

This becomes very cumbersome if we have 4 tables that need to do the full outer join to.


Please advise !

[CJ_London]

access does not support the outer join - so you'll have to keep with the union query.

However, providing your join is simple (e.g. joining all tables on say customerID) you can use a union query to gather all possible customerID's for all 4 tables - then left join this query to the 4 tables. an example for 2 tables

SELECT *
FROM 
    ((SELECT CustID from tableX
      UNION SELECT CustomerPK FROM tableY) AS A
    LEFT JOIN TableX ON TableX.CustID = A.CustID)
    LEFT JOIN TableY ON TableY.CustomerPK=A.CustID