Hope someone can help me with this. I'm trying to do a full outer join in Access across 3 tables using a union query that someone recommended (or another method if it can be done):
The common field among them is a date field in the fisrt column but the tables differ in terms of both the number of rows (dates) they have and also the number of columns/fields. Hence I want to bring back all rows from all three tables even those that are unmatched by the date field, but I want to join by date field across the tables so where there is a match, the data is aligned by the date field. I've been told I need to use a union query to do this but I am having problems.
I ran the query below, but it generates a message saying that the "number of columns in the tables does not match. But I thought the whole point of a union query was to allow this! But I've never really used SQL for Access (only oracle at basic level) so not sure if the syntax is right.
Hope someone can shed some light or suggest an alternative approach for what I need to do.
BOE LEFT JOIN indices ON bedmate=indices.date
UNION select *
technicals LEFT JOIN boe ON technicals.date=boe.date;
I ran the query below, but it generates a message saying that the "number of columns in the tables does not match. But I thought the whole point of a union query was to allow this!
Union queries (and the related difference and intersection) need to be "union compatible" which means that there must be the same number of columns and they must be the same data type (or at least be easily converted to the other). This is standard SQL and not specific to Access SQL. You will need, therefore, to list all the columns you need to use in your union query.
Although you can't use FULL OUTER JOIN syntax in Access you can replicate the same effect using 3 joins and UNION clauses.
Originally Posted by databasejournal.com
Access does not support the FULL OUTER join clause. Therefore, to simulate the FULL OUTER join functionality you need perform three different joins, and then UNION together the three different result sets. Where Set 1 would be created using an INNER JOIN, Set 2 would be created with a LEFT OUTER JOIN and Set 3 would be created using a RIGHT OUTER JOIN.