Skip to main content
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    Use of Outer Joins in Voyager Prepackaged Access Reports

    • Product: Voyager
    • Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare



    Use of Outer Joins in Voyager Prepackaged Access Reports


    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)



    Constructing custom SQL queries by request and troubleshooting unexpected results from customer-created SQL queries falls outside the scope of Support. The above has been posted for informational purposes.  Voyager-L and Developer Network are useful resources for finding helpful custom SQL or obtaining assistance from peers in troubleshooting custom queries.



    • Article last edited: 08-Jun-2021
    • Was this article helpful?