When migrating from Voyager to Alma, you may wish to identify and delete Vendors that have no currently attached Purchase Orders or Invoices.
These three queries (a main query and its two required subqueries), which you can run in Voyager's Prepackaged Access Reports, will display such Vendors and allow you to delete them if you wish.
Subquery (Name the query the following): Vendor_Usage_POcount_sq
Count(PURCHASE_ORDER.PO_ID) AS CountOfPO_ID,
Max(PURCHASE_ORDER.PO_STATUS_DATE) AS MaxOfPO_STATUS_DATE
GROUP BY PURCHASE_ORDER.VENDOR_ID;
Subquery (Name the query the following): Vendor_Usage_InvCount_sq
Count(INVOICE.INVOICE_ID) AS CountOfINVOICE_ID,
Max(INVOICE.INVOICE_STATUS_DATE) AS MaxOfINVOICE_STATUS_DATE
GROUP BY INVOICE.VENDOR_ID;
The two subqueries above are used to count purchase orders and invoices, and find the most recent status date for those objects. These subqueries are used by the following query that you will run:
Main Query (Name it whatever you wish)
Vendors with No Attached Purchase Orders or Invoices
Create and save this query (which uses the previous two subqueries) to display vendor records that have no currently attached purchase orders and no attached invoices.
SELECT VENDOR.VENDOR_NAME, VENDOR.VENDOR_CODE,
FROM ((VENDOR INNER JOIN VENDOR_TYPES
ON VENDOR.VENDOR_TYPE = VENDOR_TYPES.VENDOR_TYPE)
LEFT JOIN Vendor_Usage_InvCount_sq
ON VENDOR.VENDOR_ID = Vendor_Usage_InvCount_sq.VENDOR_ID)
LEFT JOIN Vendor_Usage_POcount_sq
ON VENDOR.VENDOR_ID = Vendor_Usage_POcount_sq.VENDOR_ID
WHERE (((Vendor_Usage_POcount_sq.VENDOR_ID) Is Null)
AND ((Vendor_Usage_InvCount_sq.VENDOR_ID) Is Null))
ORDER BY VENDOR.VENDOR_NAME;
For other queries that may be useful, see my "Surviving Almanado: tips for a successful pre-implementation" presentation's Accompanying Materials.
If you need assistance in running custom SQL queries in Prepackaged Access Reports, consult the Voyager Customer Listserv.