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

    Voyager to Alma Migration Query: Identifying Bound Withs in your database

    Created By: Laura Guy (contact)
    Created on: 11/06/2020



    When you migrate from Voyager to Alma it may be useful to identify and review what are called "Bound Withs" -- MFHDs that are attached to more than one bib record, indicating that the bibs are bound together.

    This set of three queries (two subqueries and a main query) can be run using Voyager Prepackaged Access Reports.  Depending on what you want to know, you can run one of the subqueries instead of the main query.

    If you want to know the MFHD IDs and the number of attached bibs, run Bound Withs Subquery 1.
    If you want this and also the count of attached items, run Bound Withs Subquery 2.
    If you want this and also the list of bib IDs of the attached bibs, run Bound Withs Main Query. The main query gives you more rows of results because there is one row for each bib.

    Subquery 1: This finds the MFHDs attached to multiple bibs and notes the number of attached bibs. Save it under the name: Bound Withs Subquery 1

    SELECT BIB_MFHD.MFHD_ID, Count(BIB_MFHD.MFHD_ID) AS NumAttachedBibs
    FROM BIB_MFHD
    GROUP BY BIB_MFHD.MFHD_ID
    HAVING (((Count(BIB_MFHD.MFHD_ID))>1));

    Subquery 2: For MFHDs selected by the first subquery, it counts the attached items. Save it under the name: Bound Withs Subquery 2

    SELECT [Bound Withs Subquery 1].MFHD_ID, [Bound Withs Subquery 1].NumAttachedBibs, Count(MFHD_ITEM.MFHD_ID)
    AS NumAttachedItems
    FROM [Bound Withs Subquery 1] LEFT JOIN MFHD_ITEM ON [Bound Withs Subquery 1].MFHD_ID = MFHD_ITEM.MFHD_ID
    GROUP BY [Bound Withs Subquery 1].MFHD_ID, [Bound Withs Subquery 1].NumAttachedBibs;

    Main Query:

    SELECT  [Bound Withs Subquery 2].MFHD_ID, [Bound Withs Subquery 2].NumAttachedBibs,
    [Bound Withs Subquery 2].NumAttachedItems, BIB_MFHD.BIB_ID
    FROM [Bound Withs Subquery 2] INNER JOIN BIB_MFHD ON [Bound Withs Subquery 2].MFHD_ID = BIB_MFHD.MFHD_ID
    ORDER BY [Bound Withs Subquery 2].MFHD_ID, BIB_MFHD.BIB_ID;

    For other queries that may be useful, see my "Surviving Almanado: tips for a successful pre-implementation" presentation's Accompanying Materials.

    Posted as is. If you need assistance in running custom SQL queries in Prepackaged Access Reports, consult the Voyager Customer Listserv.