Skip to Main Content
It looks like you're using Internet Explorer 11 or older. This website works best with modern browsers such as the latest versions of Chrome, Firefox, Safari, and Edge. If you continue with this browser, you may see unexpected results.

Collection Services Policy and Procedure Manual: VOYAGER

REPORTS

Creating access reports using Voyager data is extremely complex.  Kathy is the primary creator of reports for all law library departments. 

Sample queries used to creat reports are listed below:

A query that has the ability to show multiple statistical categories per item, bib id, item id,
title, and barcode.

 

SELECT BIB_TEXT.BIB_ID, BIB_TEXT.TITLE, MFHD_ITEM.MFHD_ID, MFHD_ITEM.ITEM_ID, ITEM_BARCODE.ITEM_BARCODE, ITEM_STAT_CODE.ITEM_STAT_CODE_DESC

FROM (ITEM_BARCODE INNER JOIN ((BIB_TEXT INNER JOIN BIB_MFHD ON BIB_TEXT.BIB_ID = BIB_MFHD.BIB_ID) INNER JOIN MFHD_ITEM ON BIB_MFHD.MFHD_ID = MFHD_ITEM.MFHD_ID) ON ITEM_BARCODE.ITEM_ID = MFHD_ITEM.ITEM_ID) INNER JOIN (ITEM_STATS INNER JOIN ITEM_STAT_CODE ON ITEM_STATS.ITEM_STAT_ID = ITEM_STAT_CODE.ITEM_STAT_ID) ON MFHD_ITEM.ITEM_ID = ITEM_STATS.ITEM_ID;

 

To get all POs in a ledger that have been invoiced

SELECT BIB_TEXT.TITLE, PURCHASE_ORDER.PO_NUMBER, LEDGER.LEDGER_NAME,LINE_ITEM_FUNDS.FUND_ID, PURCHASE_ORDER.PO_STATUS

FROM (((((LINE_ITEM INNER JOIN LINE_ITEM_COPY_STATUS ONLINE_ITEM.LINE_ITEM_ID = LINE_ITEM_COPY_STATUS.LINE_ITEM_ID) INNER JOIN LINE_ITEM_FUNDS ON LINE_ITEM_COPY_STATUS.COPY_ID =LINE_ITEM_FUNDS.COPY_ID) INNER JOIN LEDGER ON LINE_ITEM_FUNDS.LEDGER_ID= LEDGER.LEDGER_ID) INNER JOIN PURCHASE_ORDER ON LINE_ITEM.PO_ID =PURCHASE_ORDER.PO_ID) INNER JOIN LINE_ITEM_COPY ONLINE_ITEM.LINE_ITEM_ID = LINE_ITEM_COPY.LINE_ITEM_ID) INNER JOIN BIB_TEXT ON LINE_ITEM.BIB_ID = BIB_TEXT.BIB_ID

WHERE (((LEDGER.LEDGER_ID)="23"));

 

 This report provides similar information as the one above.  You have to change the Fiscal year number as well as the Fund nameThis gives you all POs that have been invoiced for a particular Ledger

SELECT FISCAL_PERIOD.FISCAL_PERIOD_NAME, BIB_TEXT.BIB_ID, BIB_TEXT.TITLE, MFHD_MASTER.NORMALIZED_CALL_NO, LEDGER.LEDGER_NAME, FUND.FUND_ID, FUND.FUND_NAME, FUND.PARENT_FUND, CCur(Sum([Amount]/100)) AS Amt, LINE_ITEM_COPY_STATUS.STATUS_DATE FROM (((((FISCAL_PERIOD INNER JOIN (LEDGER INNER JOIN FUND ON LEDGER.LEDGER_ID = FUND.LEDGER_ID) ON FISCAL_PERIOD.FISCAL_PERIOD_ID =LEDGER.FISCAL_YEAR_ID) INNER JOIN INVOICE_LINE_ITEM_FUNDS ON(FUND.FUND_ID = INVOICE_LINE_ITEM_FUNDS.FUND_ID) AND (FUND.LEDGER_ID =INVOICE_LINE_ITEM_FUNDS.LEDGER_ID)) INNER JOIN ((LINE_ITEM_COPY_STATUS INNER JOIN LINE_ITEM_COPY ON LINE_ITEM_COPY_STATUS.LINE_ITEM_ID = LINE_ITEM_COPY.LINE_ITEM_ID) INNER JOIN (LINE_ITEM INNER JOIN BIB_TEXT ON LINE_ITEM.BIB_ID = BIB_TEXT.BIB_ID) ON LINE_ITEM_COPY.LINE_ITEM_ID = LINE_ITEM.LINE_ITEM_ID) ON INVOICE_LINE_ITEM_FUNDS.COPY_ID = LINE_ITEM_COPY_STATUS.COPY_ID) INNER JOIN PURCHASE_ORDER ON LINE_ITEM.PO_ID = PURCHASE_ORDER.PO_ID) INNER JOIN BIB_MFHD ON BIB_TEXT.BIB_ID = BIB_MFHD.BIB_ID) INNER JOIN FHD_MASTER ON BIB_MFHD.MFHD_ID = MFHD_MASTER.MFHD_ID GROUP BY FISCAL_PERIOD.FISCAL_PERIOD_NAME, BIB_TEXT.BIB_ID,BIB_TEXT.TITLE, MFHD_MASTER.NORMALIZED_CALL_NO, LEDGER.LEDGER_NAME, FUND.FUND_ID, FUND.FUND_NAME, FUND.PARENT_FUND,LINE_ITEM_COPY_STATUS.STATUS_DATE, MFHD_MASTER.DISPLAY_CALL_NO

HAVING (((LEDGER.LEDGER_NAME)="PARALEGAL FUNDS 2012"))

ORDER BY BIB_TEXT.TITLE, MFHD_MASTER.NORMALIZED_CALL_NO, FUND.FUND_NAME;

 

This gets a list of all claimed issues with their expected dates

SELECT SERIAL_CLAIM.CLAIM_DATE, SERIAL_ISSUES.EXPECTED_DATE, PURCHASE_ORDER.PO_NUMBER, CLAIM_TYPES.CLAIM_TYPE_DESC, BIB_TEXT.TITLE, BIB_TEXT.ISSN, SERIAL_ISSUES.ENUMCHRON, SERIAL_CLAIM.NOTE

FROM ((((SERIAL_ISSUES INNER JOIN (SERIAL_CLAIM INNER JOIN CLAIM_TYPES ON SERIAL_CLAIM.CLAIM_TYPE = CLAIM_TYPES.CLAIM_TYPE) ON (SERIAL_CLAIM.COMPONENT_ID = SERIAL_ISSUES.COMPONENT_ID) AND (SERIAL_ISSUES.ISSUE_ID = SERIAL_CLAIM.ISSUE_ID)) INNER JOIN LINE_ITEM_COPY_STATUS ON SERIAL_CLAIM.COPY_ID = LINE_ITEM_COPY_STATUS.COPY_ID) INNER JOIN LINE_ITEM ON LINE_ITEM_COPY_STATUS.LINE_ITEM_ID = LINE_ITEM.LINE_ITEM_ID) INNER JOIN PURCHASE_ORDER ON LINE_ITEM.PO_ID = PURCHASE_ORDER.PO_ID) INNER JOIN BIB_TEXT ON LINE_ITEM.BIB_ID = BIB_TEXT.BIB_ID;

 

Query to find all the BIBs that were added to a specific location during a specific time period 

SELECT BIB_MASTER.LIBRARY_ID, BIB_MASTER.BIB_ID, BIB_TEXT.TITLE_BRIEF, BIB_MASTER.CREATE_DATE, BIB_MASTER.SUPPRESS_IN_OPAC, LOCATION.LOCATION_NAME FROM ((MFHD_MASTER INNER JOIN LOCATION ON MFHD_MASTER.LOCATION_ID = LOCATION.LOCATION_ID) INNER JOIN BIB_MFHD ON MFHD_MASTER.MFHD_ID = BIB_MFHD.MFHD_ID) INNER JOIN (BIB_MASTER INNER JOIN BIB_TEXT ON BIB_MASTER.BIB_ID = BIB_TEXT.BIB_ID) ON BIB_MFHD.BIB_ID = BIB_TEXT.BIB_ID WHERE (((BIB_MASTER.LIBRARY_ID)="2") AND ((BIB_MASTER.CREATE_DATE) Between [StartDate] And [EndDate]) AND ((LOCATION.LOCATION_NAME) Like "law,fl0")); 

If your library is the only "owning library" in the database, then you can eliminate the Library ID criteria. As for location, you need to input your appropriate location code where you see my Like "law,fl0" and when selecting by date, be aware that Access considers a date to be at midnight of  the date. Therefore, if you want things created on 10/7/2013, your start date should be 10/7/2013 and your end date should be 10/8/2013.

 

 

 

 

 

800 N. Harvey Oklahoma City, OK 73102 405.208.5271