Using MS Access/Outer Join

Advertisement


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

UNION

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.


Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Richard Rost

Expertise

I am happy to answer any kinds of questions about Microsoft Access - from basic table design to advanced VBA programming. Also, please feel free to check the Access Tips & Tricks section of my web site, and free Access 101 tutorial.

Experience

I am the president of AccessLearningZone.com and 599CD.com. I specialize in Microsoft Access Tutorials. I have been teaching Access in the classroom since 1994, and online since 2002.

Education/Credentials
I am a self-taught Access expert. I have been building databases for clients since the early 90s. You can see a sample of my Access Tutorials on my web site at 599CD.com

©2016 About.com. All rights reserved.