Question

Use of Outer Joins in Voyager Prepackaged Access Reports

Answer

In Access, "Joins" combine rows from two or more tables by linking on a common field.

There are "Inner Joins" and "Outer Joins".

Inner Joins are also called "Simple Joins" and return only those records that have a match in both tables:

An Outer Join returns records from the left, right, or both tables (depending on the type of join), even if there is no matching record in the other table:

You use an Outer Join to avoid dropping records when two tables do not have matching values.

The following example is a Left Outer Join between the ITEM and the ITEM_BARCODE tables.  It will not exclude unbarcoded items from the result set:

If you do not use an Outer Join in these cases:

• Unbarcoded items will not be returned by a query that uses the ITEM_BARCODE table.
• E-resource MFHDs without items will not be returned in a query that uses the ITEM table.
• BIBs without subject heading subdivisions will not be returned in a query that uses the BIB_SUBDIVISION table.

To create a join in MS Access:

1. In the Query Design View, draw a line between fields in two tables
2. Right-click on the join to open "Join Properties"
3. In most Outer Join cases you will select the second option (left join)

• Article last edited: 08-Jun-2021