Voyager to Alma Migration Query: Item Types in Use as Temporary or Permanent Types

When migrating from Voyager to Alma, you may want to simplify your item types.  This main query and subquery work together to count the occurrences of item type codes as temporary or permanent item types.

This query, which can be run using Voyager's Prepackaged Access Reports, shows item type code, item type name, permanent or temporary, and number of uses.

Subquery: This subquery lists item_ids, item type ids, and a field that shows whether the item type is permanent or temporary.  Save it under the name “Item Types in Use Subquery” but don’t run it:

SELECT ITEM.ITEM_ID, ITEM.ITEM_TYPE_ID, "Perm"
AS Perm_Temp FROM ITEM
UNION ALL
SELECT ITEM.ITEM_ID, ITEM.TEMP_ITEM_TYPE_ID, "Temp"
AS Perm_Temp FROM ITEM;

Main Query: This compares your list of item types to the results of the subquery and counts the occurrences.  Save it under any name you wish and run it.

SELECT ITEM_TYPE.ITEM_TYPE_CODE, ITEM_TYPE.ITEM_TYPE_NAME,
[Item Types in Use Subquery].Perm_Temp,
Count([Item Types in Use Subquery].ITEM_TYPE_ID) AS Count
FROM ITEM_TYPE LEFT JOIN [Item Types in Use Subquery]
ON ITEM_TYPE.ITEM_TYPE_ID = [Item Types in Use Subquery].ITEM_TYPE_ID
GROUP BY ITEM_TYPE.ITEM_TYPE_CODE, ITEM_TYPE.ITEM_TYPE_NAME,
[Item Types in Use Subquery].Perm_Temp
ORDER BY ITEM_TYPE.ITEM_TYPE_CODE, [Item Types in Use Subquery].Perm_Temp;

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.