Access Left Outer Joins

In our prior post we reviewed how to make a relationship to “join” two tables together using a query to produce a single data set. This query allowed us to see our employees and their related hours.

The join that we made between these two tables is called an inner join. An inner join takes the common records between the two data sets and displays just those common records. An easy example of this can be shown as:

Table A has employees Tyler and Steve
Table B has employees Bob and Tyler

If we were to join these tables together using an inner join, only the Tyler records would show.

To get around this predicament, we can use a left outer join.

Left Outer Joins

A left outer join allows you to display ALL of the records from table A as well as the records from table B where the join conditions are met. Using the above example, our query would show records for both Tyler and Steve, while still excluding Bob.

Jumping back into our database, take a look at the records we have in our query. We have three records, three employees. Open up the Employee_Data table and notice that we actually have four employees in this table; Taylor Lacky is missing.

This is because Taylor Lacky’s EMPLID, 56789 does not exist in Employee_Hours, so we only show the three matched employees.

 

 

Changing the Join Type

When we made our join in the last post we made an innner join, Access’ default join type. We clicked on EMPLID in Employee_Data and drag-and-dropped it onto EMPLID in Employee_Hours. This motion established our Left (A) and Right (B) tables. Table A being Employee_Data and table B being Employee_Hours.

To change the inner join we have to a left outer join

  1. Double Click the line that is joining the two tables
  2. Choose the second option
  3. Click OK

You should now see a line with an arrow rather than just a normal line joining the two tables.

Run your query again and you should now see employee data for Lacky,Taylor while the hours information is null (blank).

Next we will go over joining multiple tables together, making unions,  and writing expressions (formulas).


If you liked this content and found it helpful, please like the page and leave a comment below. You can also ask questions on the Forum to get personalized help on your issues and questions.



Categories: Access

Tags: , , ,

Leave a Reply

Show Buttons
Hide Buttons
%d bloggers like this: