Using MS Access/Outer Join
KentGorrell wrote at 2014-12-31 02:36:51
he Access Query Designer will not provide an Outer Join.
Richard's answer won't provide you with what you asked for. This will only give you a Left or Right Join with all the records from one table and only those that match from the other.
To get an Outer Join, that will give you all records from both tables, you first need to create a Union Query which will provide all records from both tables. The Query Designer can't create or show the design of a Union query so you will need to do this in SQL view.
What you haven't told us is if the records for each person have the same ID in both tables so let's just use the name. Your query should look like -
SELECT Customer_Name FROM tblOne
SELECT Customer_Name FROM tblTwo
Now when you save this and view it you will see that all names from both tables are listed whether they are in the other table or not. The other interesting thing about a UNION query is that it returns only one instance of each name.
Now go back to the query designer and drop this Union query on it with the other two tables.
Create a Join from the UNION query to the Customer_Name field in the other two tables. At this point we need to right click on each of those joins and select the option to return all records from the Union query and only those from the table where the join is equal.
Now you will get all records from both tables whether they exist in the other or not.