When you create a query that uses a join between tables, Access will create the join as an inner join by default. This means
that the tables on both sides of the join must have matching records in order for those records to be displayed. There are two other types of
joins as well, left outer and right outer joins.
To specify the type of join being used, double-click on the join line between the two tables. When you double-click, the Join Properties
dialog box appears, as shown here:
Only include rows where the joined fields from both tables are equal.
If a project entered in the 'Projects' table does not have any hours assigned to it in the 'Hours' table, that project will not be displayed.
Left outer join
Include ALL records from 'Projects' and only those records from 'Hours' where the joined fields are equal.
Left outer joins are used when you want to display all records from one of the tables regardless of whether there is a corresponding entry in
the other table used. For example, if you want to see all the projects regardless of whether any hours have been entered, you would use a left
outer join. When you specify a left outer join, you will see an arrow pointing toward the second table in the join, as seen here:
Right outer join
Include ALL records from 'Hours' and only those records from 'Projects' where the joined fields are equal.
Right outer joins are the same as left outer joins, just flipped around. They are used less than the other two joins because you can specify
the tables in the order you desire and use a left outer join. Using a right outer join with the 'Projects' and 'Hours' tables, you can see
which records are in 'Hours' that dont have a legitimate projects associated with them. Right outer joins are good for checking out data
integrity. When you specify a right outer join, the arrow points to the first table in the join, as shown here:
In the next lesson, you will learn about the different types of queries and where you can use them.
Specifying Join Properties For Queries - Exercise