Video: Create queries with outer joins

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:

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.

  

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.

   

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.