Outer Joins
By Richard Rost
6 years ago
Missing Records! Show All Records with Outer Join
Have you ever put two related tables together in a query (like customers and contacts) and you wanted to print out a full list of customers and their related orders, but some of your customers were missing? Well, in this lesson I'll show you how to use an OUTER JOIN to fix that problem.
Holden from Montgomery, Alabama (a Gold Member) asks: I am trying to put together a single query showing all of my customers along with a list of all of their orders. However, when I join them together in a query I find that if the customer doesn't have any orders, they don't show up in the list. I started this database a year ago and I have customers going back 20 years, but I didn't import all of their old orders. How can I fix my query to show ALL of the customers whether or not they have orders in the system?
Members
I'll show you how to show all customers who have NO contacts, and then who you haven't contacted in the past year. Next we'll make a printable contact report with grouping levels for each customer and their contact history below them. Then we'll learn about the can grow and can shrink properties to hide blank space in the report.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
 Links
Access Expert 1: https://599cd.com/acx1
Relationships: https://599cd.com/relationships
Aggregate Query: https://599cd.com/aggregate
Imaging Seminar: https://599cd.com/imaging
Outer Joins: https://599cd.com/outerjoin
Subscribe to Outer Joins
Get notifications when this page is updated
Intro
In this video, I will show you how to use an outer join in Microsoft Access to display all records from one table even when there are missing related records in another table. We will talk about the difference between inner joins and outer joins, demonstrate how to adjust your query in Access to show all customers whether or not they have orders or related data, and review the key join properties needed to achieve this in your database queries.
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I'm going to show you how to use an outer join to show all records when you join two tables together in a query and you're missing records. Where did they go? In this video, we'll talk about that.
Today's question comes from Holden from Montgomery, Alabama, one of my Gold members. Holden says, I'm trying to put together a single query showing all of my customers along with a list of all of their orders. However, when I join them together in a query, I find that if the customer does not have any orders, they do not show up in the list. I started this database a year ago and I have customers going back 20 years, but I did not import all of their old orders. How can I fix my query to show all of the customers whether or not they have orders in the system?
This is one of those frequently asked questions I get a lot. I've got two tables. I join them together with a query and I want to see all the records, but I'm missing some of them. Why is that? Sometimes you know the problem, but you don't necessarily know the terminology.
Let's go over some terminology real quick when it comes to queries. There are two types of queries we're going to talk about in this lesson. There's an inner join and an outer join.
An inner join is the default. It's the kind that Access makes unless you change it. An inner join will show all records from both tables where there are matching records in both tables. For example, if you join customers and contacts or customers and orders, it does not matter. Any two tables. As long as you have a matching value in each table, you will see that record. If you have customers that are missing contacts, you will not see those customers. Just like if there are contacts in the system that do not have customers assigned to them, you will not see those contacts.
The other kind of query is called an outer join. An outer join shows all records from the left table and matching data from the right table where it exists. An outer join is sometimes called a left outer join or just a left join. You might see all those terms used.
Basically, this will show all of your customers. For those customers that have contacts, you will see the matching contact information. If they are missing contacts, you will still see the customer. You can go the other way too. There is also something called a right join, but that's for a different lesson.
You can tell the difference because of the little arrow right there. See the arrow head? That's how you know you have an outer join.
How do we set this up in Access? Let's take a look.
This is my basic customer and contact template. You can download a free copy of this off my website. I will put a link down in the description below the video. There are two tables: customers and contacts. A customer is obviously a customer. A contact is any time I talk to that customer.
For example, if I open up my customer form here, here's me, the customer info, and here are the contacts for this customer. On this date and time, this happened. On 12/12, called about a job. Then came in for an interview.
If I go to the next customer, Jim Kirk fought Klingons on that date, and so on. These are all the contacts.
It does not matter what the second table is. It could be orders, products, or anything related to customers. Notice that some of these people do not have contacts. Deanna Troy has no contacts, just like Jean-Luc Picard, no contacts.
As a side note, if you have not watched my free video yet on relationships between tables, go watch that now to help you get a good understanding of relationships before you get into topics like outer joins. I will put a link to that in the links section below.
If I make a query, go up to Create - Query Design. If I bring in my customers and then contacts, you can see that Access makes a normal join here. That is an inner join. That means it is going to match records from both tables as long as there are matching records in each table: CustomerID here, CustomerID there.
If I bring in CustomerID, first name, and last name, and then over here I want to see their contacts, so I add the contact date and the description, and save this (Ctrl+S) as my ContactQ.
Now if I run this, I'm only seeing Richard and James. I do not see Deanna and Jean-Luc. Why? That is because it's an inner join. I do not see any records where there are not matching records in both tables.
How do we fix that? Go back to Design View. Double-click on this little line right here to edit the join properties. Left table: CustomerT. Right table: ContactT. The column that joins is the CustomerID.
Notice option 1 is set: Only include rows where the joined fields from both tables are equal. What I want is option 2 for a left join, a left outer join: Include ALL records from CustomerT, and only those records from ContactT where the joined fields are equal.
You can go the other way - that's a right join here. Don't worry about that. Hit OK.
Now notice I have the arrow there. If I run the query, look at that. I am seeing all of the customers, even the ones missing contact info, and the contact info where it exists.
Now you can take this query and use it to generate your report or anything you want to show all of your customers and their related orders. If this is order information, you could make an aggregate query where you group together all of the customers so that you see one record for each customer and then sum up their order total, for example. That is called an aggregate query. I have videos on that. I'll put a link in the link section.
If you want something like a count of the number of contacts or when their first order was placed, that can be done with an aggregate query. This should answer your question right now, Holden. You can go ahead and make that query where you see all the customers and their order information if it exists.
I cover a lot more about joins in my Access Expert Level 1 class. It has all the basics about table relationships, all the different join types, making relational combo boxes, and lots more. That is Access Expert Level 1. Again, I will put a link in the link section below.
Want to learn more about joins in the extended cut for members? I show how to find the opposite of what we just found. I want to see all the customers that have no contacts in the system, where their contact information is null. Then we are going to add to that: how do I see all customers I haven't contacted in the past year? We will add an OR condition, where their contact information is either null or older than a year. I will show you how to do both of those.
Then we're going to build a nice, pretty contact report. As you can see on the screen, I am going to cover sorting and grouping levels so each customer has their own group. You can see the gray bands there. In each group, you will see the contact records one at a time, so it does not repeat. You don't see the customer with each contact like you do in the query. Reports let you group stuff together.
Then I'll teach you about Can Grow and Can Shrink. This way, if a customer does not have any contact information, like Deanna Troy and Jean-Luc, they have no contact information, we will make their section shrink. You still see it, but there is nothing there and there is no wasted space.
That is all covered in the extended cut for members. Silver Members and up get access to all of my extended cut videos.
How do you become a member? Click on the Join button below the video. Silver Members and up get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks.
After you click the Join button, you will see a list of all the different membership levels that are available, each with its own special perks.
These TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they will always be free.
Quiz
Q1. What issue did Holden experience when trying to join his Customers and Orders tables in a query?
A. Customers without orders were missing from the query results
B. Orders without customers were missing from the query results
C. Duplicate records were appearing in the query
D. The query was showing unrelated records from other tables
Q2. What is the default type of join that Access creates when joining two tables?
A. Outer join
B. Inner join
C. Right join
D. Cross join
Q3. How does an inner join work in Access?
A. It shows all records from both tables, regardless of matching values
B. It shows records only where there are matching values in both tables
C. It shows all records from the left table and matching records from the right table
D. It shows unmatched records only
Q4. What is another name for an outer join that shows all records from the left table?
A. Right join
B. Inner join
C. Full join
D. Left join
Q5. How can you tell visually in Access if a join is an outer join instead of an inner join?
A. The join line is red
B. There is an arrowhead on the join line
C. The join line is dashed
D. There is no line at all
Q6. How do you change the join type in Access query design?
A. Change the format of the table
B. Double-click the join line and set the appropriate join option
C. Right-click and select 'Convert to Outer Join'
D. Use a macro to change the join type
Q7. What does option 2 in the join properties dialog specify?
A. Include only records where fields from both tables match
B. Include all records from the left table, and matching records from the right table
C. Include all records from the right table, and matching records from the left table
D. Only unmatched records
Q8. What can you use the outer join query for once it is set up correctly?
A. To create reports that show all customers and their related orders, even if some have none
B. To filter out customers with no orders
C. To delete customers with no contacts
D. To only display records with matching fields in both tables
Q9. What kind of query groups records together and can, for example, count contacts or sum order totals?
A. Update query
B. Make-table query
C. Aggregate query
D. Delete query
Q10. In the extended cut for members, what additional topic will be covered?
A. How to import customers from Excel
B. How to show only customers with no contacts
C. How to create automatic relationships
D. How to create a database backup
Answers: 1-A; 2-B; 3-B; 4-D; 5-B; 6-B; 7-B; 8-A; 9-C; 10-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's video from Access Learning Zone covers how to use an outer join in Microsoft Access to ensure that all records from one table appear in your query results, even if there are missing related records in the joined table. This is a common issue when working with two tables, such as customers and their orders or contacts, and you notice that some customers are missing from your query output because they have no corresponding records in the second table.
The scenario comes from a student who wants to display every customer along with their orders, but only customers with orders show up in the results. This happens because, by default, Access uses what is called an inner join when you join two tables in a query. An inner join only shows records that have a match in both tables. So, if a customer does not have any order records, they are excluded from the results entirely.
It is important to understand the basic terminology about joins in queries. An inner join, which is the default in Access, matches records from both tables only when there are matching values. So if you are joining customers and orders on CustomerID, only those customers who have at least one order record appear in the query. Any customers without any orders will not be visible. The same logic applies if you join customers and contacts—customers without contacts are hidden.
To address this, you need to use what is called an outer join. Specifically, a left outer join will display all records from your main (left) table, in this case customers, and bring in related data from the joined (right) table, such as orders or contacts, only where matching records exist. If there are no related records, Access shows null values for the fields from the right table, but the customer information itself is still displayed in the results.
In Access, the join type is represented by a line between the two tables in Design View. A straight line is an inner join, while a line with an arrow (or a "1" and an infinity symbol) indicates an outer join. To convert an inner join to an outer join, open the query in Design View, then double-click on the line connecting the two tables to open the join properties window. Here, you will see options. The first option is the inner join, which only includes records where there are matching values in both tables. The second option is the left outer join, which includes all records from the left table and only matching records from the right table.
Select the second option to create a left outer join. This ensures that every customer appears in your query result set, regardless of whether they have corresponding contacts, orders, or any other related data. You will still see data from the right table if it exists, and null values otherwise.
Once you set up your outer join, you can use this query as the basis for reports or further analysis. For example, you could create a summary query to group customers and calculate aggregate values like the total number of orders, the total sales, or identify which customers have never been contacted or placed an order. This is where aggregate queries come into play, allowing you to group by customer and perform calculations on the related records.
If you are new to relationships between tables in Access or concepts like primary and foreign keys, I recommend reviewing my free video on table relationships before working with joins. Understanding these concepts will make outer joins much easier to understand and implement.
I also discuss additional examples and advanced join techniques in my Access Expert Level 1 class, where I cover different join types, creating relational combo boxes, and a lot more. If you are interested in going further with joins and advanced query techniques, details for that course are available on my website.
In today's Extended Cut for members, I go even further and show how to find customers who do not have any related contact records at all by searching for nulls in the related table. I also explain how to filter customers who have not been contacted in the past year, adding an OR condition for records that are either missing or outdated. Then I walk you through constructing a grouped contact report that summarizes each customer and their related contacts, using features like sorting, grouping, and Access report properties such as Can Grow and Can Shrink to make the display compact and readable. This approach ensures customers without any contact information do not take up extra space in the report, but are still represented.
Silver Members and above can watch the full Extended Cut and all other advanced topics. Joining is simple. You will find a full list of membership levels and their benefits on my website.
Remember, my TechHelp videos will keep coming as long as you keep tuning in, and they will always be free.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.
Topic List
Understanding inner joins in Access queries
Understanding outer joins in Access queries
Difference between inner join and outer join
Identifying missing records with inner joins
Setting up an outer join in Access Query Design
Editing join properties in Access
Using left join to show all customer records
Displaying records with or without related data
Recognizing the arrow indicator for outer joins
Building a query to show all customers and their contacts
Saving and running the modified query with an outer join
|
