Outer join

[texasalynn]

Forgive me if this is dumb!

If there a way to get a join to give the results two tables without a commonality.

For example - I have a tables with data for each month. If I bring into a query two tables and want to get all the data with all store numbers. So a store might only be in one table not both.

My boss thought this would be an outer join. I said no. That is not possible because the tables need to be linked and then you will only get an outer join of one table. Have I missed something?

I hope this makes sense to someone and can give me an explanation.

Thanks . . .
texasalynn

 

[Pat Hartman]

You actually need a "full outer join" which Access does not directly support. To simulate a full outer join requires three queries.

query1 - left join tbl1 to tbl2

query2 - right join tbl1 to tbl2

query3 - union query1 with query2

When you run query3, it automatically runs queries 1 and 2.

Query3:
Select * from query1
Union Select * from query2;

query1 obtains all the rows in tbl1 and any matching data from tbl2.
query2 obtains all the rows in tbl2 and any matching data from tbl1.
query3 pulls the two recordsets together and eliminates duplicates (the rows where there is data in both tables).

 

[Jon K]

You can also do it in one query like this:-

SELECT *
FROM Table1 LEFT JOIN Table2 ON Table1.StoreNumber = Table2.StoreNumber;
UNION
SELECT *
FROM Table1 RIGHT JOIN Table2 ON Table1.StoreNumber = Table2.StoreNumber;