Skip to main content
ExLibris
  • Subscribe by RSS
  • ExLibris Dev

    How to get list of print book holdings in database?

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

    Question

    How can a list of print book holdings in Voyager be generated?

    Answer

    Query the database and export results to Excel. In the query below, "print holding" is determined by presence of ISBN. Query can be customized locally with a more refined set of parameters based on local MARC data.

    1. Open Prepackaged Reports (Reports.mdb)
    2. Create > Query Design > click Close in Show Table box
    3. Click SQL View in menu bar.
    4. Paste query into view (query only checks for presence or absence of ISBN; apply additional parameters as needed to suit local data):
    SELECT MFHD_MASTER.DISPLAY_CALL_NO
    , BIB_TEXT.TITLE
    , LOCATION.LOCATION_NAME
    , BIB_TEXT.NETWORK_NUMBER
    ,BIB_TEXT.ISBN
    , BIB_TEXT.BIB_ID
    FROM MFHD_MASTER
    ,LOCATION
    ,BIB_TEXT
    ,BIB_MFHD
    WHERE MFHD_MASTER.LOCATION_ID = LOCATION.LOCATION_ID
    and BIB_TEXT.BIB_ID = BIB_MFHD.BIB_ID
    and BIB_MFHD.MFHD_ID=MFHD_MASTER.MFHD_ID
    and BIB_TEXT.ISBN is not null
    ORDER BY BIB_TEXT.TITLE
    ,BIB_TEXT.BIB_ID
    ,BIB_TEXT.ISBN;
    1. (optional) Click Save to save the query and give it a name.
    2. Click Run in menu bar.
    3. Click External Data menu > Export: Excel
    4. Give file name & format.

    Additional Information

    For serials holdings, a similar query using BIB_TEXT.ISSN can be constructed.

     

    To add usage statistics tracked in CIRC_TRANSACTIONS and CIRC_TRANS_ARCHIVE, join MFHD_ITEM table to join the circulation transaction tables to the query. ITEM table can also be joined to include HISTORICAL_BROWSE total.

     

    Example of joining ITEM to include HISTORICAL_BROWSE:

    SELECT MFHD_MASTER.DISPLAY_CALL_NO
    , BIB_TEXT.TITLE
    , LOCATION.LOCATION_NAME
    , BIB_TEXT.NETWORK_NUMBER
    , BIB_TEXT.ISBN
    , BIB_TEXT.BIB_ID
    , ITEM.HISTORICAL_BROWSE
    FROM MFHD_MASTER
    ,LOCATION
    ,BIB_TEXT
    ,BIB_MFHD
    ,MFHD_ITEM
    ,ITEM
    WHERE MFHD_MASTER.LOCATION_ID = LOCATION.LOCATION_ID
    and BIB_TEXT.BIB_ID = BIB_MFHD.BIB_ID
    and BIB_MFHD.MFHD_ID=MFHD_MASTER.MFHD_ID
    and BIB_MFHD.MFHD_ID=MFHD_ITEM.MFHD_ID
    and MFHD_ITEM.ITEM_ID=ITEM.ITEM_ID
    and BIB_TEXT.ISBN is not null
    ORDER BY BIB_TEXT.TITLE
    ,BIB_TEXT.BIB_ID
    ,BIB_TEXT.ISBN;

    • Article last edited: 06-Apr-2017
    //doorbell.io feedback widged