Voyager to Alma Migration Query: Counts and Types of Patron Records in the Database

When migrating from Voyager to Alma, it may be helpful to review the counts and types of patron records present in the database.

This query and a make table subquery, which can be run using Voyager Prepackaged Access Reports, will provide such an overview, including number of patron records present, along with status of accounts and barcodes by patron group.  Note that some patrons may have multiple patron groups.

Run this Make Table query first:

SELECT PATRON_BARCODE.PATRON_ID, PATRON_BARCODE.PATRON_GROUP_ID, IIf([patron_barcode].[barcode_status]='1','Barc Active','Barc Inactive') AS Barc_Status, IIf([patron].[expire_date]>Now(),'Pat Rec Active','Pat Rec Expired') AS Patron_Status INTO [Patron Groups Assigned to Locals Subquery]
FROM PATRON_BARCODE INNER JOIN PATRON ON PATRON_BARCODE.PATRON_ID = PATRON.PATRON_ID
WHERE (((PATRON.DB_ID)='0' Or (PATRON.DB_ID) Is Null));

Then run this query, which uses the table created in the previous query:

SELECT PATRON_GROUP.PATRON_GROUP_CODE, PATRON_GROUP.PATRON_GROUP_NAME, [Patron Groups Assigned to Locals Subquery].Barc_Status, [Patron Groups Assigned to Locals Subquery].Patron_Status, Count([Patron Groups Assigned to Locals Subquery].PATRON_GROUP_ID) AS [Count]
FROM PATRON_GROUP LEFT JOIN [Patron Groups Assigned to Locals Subquery] ON PATRON_GROUP.PATRON_GROUP_ID =
[Patron Groups Assigned to Locals Subquery].PATRON_GROUP_ID
GROUP BY PATRON_GROUP.PATRON_GROUP_CODE, PATRON_GROUP.PATRON_GROUP_NAME, [Patron Groups Assigned to Locals Subquery].Barc_Status, [Patron Groups Assigned to Locals Subquery].Patron_Status
ORDER BY PATRON_GROUP.PATRON_GROUP_CODE, [Patron Groups Assigned to Locals Subquery].Barc_Status, [Patron Groups Assigned to Locals Subquery].Patron_Status;

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.