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

    Subqueries in Voyager Prepackaged Access Reports

    1. Last updated
    2. Save as PDF
    • Product: Voyager
    • Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare

     

    Question

    Example of using a subquery in a Prepackaged Access Reports query.

    Answer

    Subqueries fill the same needs in Prepackaged Access Reports as Make Table queries, but in a different way.  You usually use them when you want to write a query, but one of the tables you need does not exist.

    But unlike Make Table queries, subqueries don't save the intermediate results in your Access database, and therefore don't make your Access database much (sometimes very much!) larger.

    Subquery example:

    Let's say you want to find all Patrons with duplicate barcodes.  In other words, barcodes that appear more than once in the database.

    First, write the subquery (we're using the Sigma Tool to get a "Total" row):

    clipboard_ed4fa5015287c54edac0f545b21f040fd.png

     

    Save this query with a name like "Dup patron barcodes subquery":

    clipboard_e69039444ff38f93f66c92fab1291a7bb.png

     

    You can run your subquery to check the results.  This one will list all the patrons that have duplicate barcodes (in other words, all the patron barcodes that appear more than once in the database):

    Patron_Barcode

    Num_Occurrences

    20999000012300

    2

    20999000076500

    3

    20999000034500

    2

     

    Second, write the main query.  When you are selecting tables for the main query, click on the Queries tab and select your "Dup patron barcodes subquery"

    clipboard_ec210a95417929af4ad66119067b24c62.png

     

    Then click the Tables tab and add your other tables:

    clipboard_efb23c1e0143b663776f430851024aa42.png

     

    Add your links, save the query and run it:

    clipboard_eb473816b1b018ebc6783ab727ee8d6ce.png

     

    Additional Information

    Remember that you can't have two objects with the same name, so it is wise before you start with subqueries to come up with a naming convention to help you remember what are main queries versus subqueries.  Support recommends you put “Main query” in the name of main queries as a reminder that there’s a subquery.

     

    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: 24-Jun-2021