Related topics
×
Manage data with queries
Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.
Manage data with queries

Create queries with outer joins

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Try it!

Sometimes in your queries, you want to see all the records from one table, along with the matching records from another table. To do this, create outer joins.

Inner joins only display data when there is a matching ID in both connected data sources. For example, if you want to display the products and quantities sold, you create an inner join between the Products and Order Details tables. When you run the query, only products that occur in orders, and thus appear in both tables, are returned.

In contrast, outer joins return all the data from one data source and some of the data from another table or data source.

For example, if you want to see which products haven’t sold, you create a query that shows the Product ID from the Products table and any matching Order IDs from the Order Details table. Products that don’t have matching records in the Order Details table return the product name with no value for the Order ID field.

Left outer joins and right outer joins

In an outer join, the table that displays all its records is called the base table. The table that displays only some (matching) records is called the secondary table.

The Join Properties dialog box describes what you see with different types of joins:

Screen shot of three tables and their Join Properties

Suppose you’ve created a query to show which products your customers bought. Your output fields are Product Name, Order ID, and Quantity.

  1. Inner join. Only include rows (records) in which the joined fields from both tables are equal, for example, only the products that have orders associated with them.

  2. Left outer join. Include all records from the table under Left Table Name and only those records from the table under Right Table Name in which the joined fields are equal, for example, all orders, but only the quantities and Product IDs for those orders that have products associated with them.

  3. Right outer join. Include all records from the table under Right Table Name and only those records from the table under Left Table Name in which the joined fields are equal, for example, all the products, even when there are no orders for them, in which case blank values appear in the Order ID and Quantity fields.

As you can tell from these examples, it’s important to know which table is on the left and which is on the right, which can actually be different from how the tables are arranged in the query window. To understand which data you’ll get from your query, read the text carefully in the Join Properties dialog box.

1. Add data sources

  • Select Create > Query Design.

Add all the tables and queries you need as data sources. (Don’t worry. If you forget one, you still have an opportunity to add more data sources later.)

If it detects or deduces them, Access may show relationships for the data sources.

2. Add a join

  • If you don’t see a relationship between the sources, add a join. Drag from the field you want in one data source to the corresponding field in another data source. The table you drag from is the table that will appear on the left in the Join Properties dialog box. By default, Access creates an inner join. If you need an outer join, change its type.

    screen shot of join between two tables

3. Change a join

  1. Double-click the line representing the join you want to change.

  2. In the Join Properties dialog box, to create an outer join, select the second or third option. Then, select OK.

    Remember to pay attention to which table is the base table—the one from which all records are displayed.

    Screenshot of Join Properties highlighting left table name

4. Complete the query

Follow the usual steps for creating a query: add output fields, add any criteria, and run or save your query. To learn how, see Create basic queries.

Want more?

Create basic queries

Excel training

Outlook training

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×