Question

Examples of IPEDS reporting queries for Voyager Prepackaged Access Reports.

Answer

These were contributed by a Voyager customer site.  No attempt was made to vet them.  Some modification may be required.

IPEDS Bib count by format location and item type

SELECT Count(BIB_TEXT.BIB_ID) AS CountOfBIB_ID, BIB_TEXT.BIB_FORMAT, LOCATION.LOCATION_NAME, ITEM_TYPE.ITEM_TYPE_NAME

FROM ((MFHD_ITEM INNER JOIN ITEM ON MFHD_ITEM.ITEM_ID = ITEM.ITEM_ID) INNER JOIN ITEM_TYPE ON ITEM.ITEM_TYPE_ID = ITEM_TYPE.ITEM_TYPE_ID) INNER JOIN (((BIB_TEXT INNER JOIN BIB_MFHD ON BIB_TEXT.BIB_ID = BIB_MFHD.BIB_ID) INNER JOIN (LOCATION INNER JOIN MFHD_MASTER ON LOCATION.LOCATION_ID = MFHD_MASTER.LOCATION_ID) ON BIB_MFHD.MFHD_ID = MFHD_MASTER.MFHD_ID) INNER JOIN BIB_MASTER ON BIB_TEXT.BIB_ID = BIB_MASTER.BIB_ID) ON MFHD_ITEM.MFHD_ID = MFHD_MASTER.MFHD_ID

GROUP BY BIB_TEXT.BIB_FORMAT, LOCATION.LOCATION_NAME, ITEM_TYPE.ITEM_TYPE_NAME, BIB_MASTER.SUPPRESS_IN_OPAC

HAVING (((BIB_MASTER.SUPPRESS_IN_OPAC)="N"));

IPEDS Bib count by format and location

SELECT Count(BIB_MASTER.BIB_ID) AS CountOfBIB_ID, BIB_TEXT.BIB_FORMAT, LOCATION.LOCATION_NAME

FROM ((BIB_TEXT INNER JOIN BIB_MFHD ON BIB_TEXT.BIB_ID = BIB_MFHD.BIB_ID) INNER JOIN (LOCATION INNER JOIN MFHD_MASTER ON LOCATION.LOCATION_ID = MFHD_MASTER.LOCATION_ID) ON BIB_MFHD.MFHD_ID = MFHD_MASTER.MFHD_ID) INNER JOIN BIB_MASTER ON BIB_TEXT.BIB_ID = BIB_MASTER.BIB_ID

GROUP BY BIB_TEXT.BIB_FORMAT, LOCATION.LOCATION_NAME, BIB_MASTER.SUPPRESS_IN_OPAC

HAVING (((BIB_MASTER.SUPPRESS_IN_OPAC)="N"));

IPEDS Item Count by Location

SELECT LOCATION.LOCATION_NAME, Count(ITEM.ITEM_ID) AS CountOfITEM_ID

FROM (ITEM INNER JOIN MFHD_ITEM ON ITEM.ITEM_ID = MFHD_ITEM.ITEM_ID) INNER JOIN LOCATION ON ITEM.PERM_LOCATION = LOCATION.LOCATION_ID

GROUP BY LOCATION.LOCATION_NAME;

IPEDS Item Count by Location and Type

SELECT LOCATION.LOCATION_NAME, Count(ITEM.ITEM_ID) AS CountOfITEM_ID, ITEM_TYPE.ITEM_TYPE_NAME

FROM ((ITEM INNER JOIN MFHD_ITEM ON ITEM.ITEM_ID = MFHD_ITEM.ITEM_ID) INNER JOIN LOCATION ON ITEM.PERM_LOCATION = LOCATION.LOCATION_ID) INNER JOIN ITEM_TYPE ON (ITEM.ITEM_TYPE_ID = ITEM_TYPE.ITEM_TYPE_ID) AND (ITEM.ITEM_TYPE_ID = ITEM_TYPE.ITEM_TYPE_ID)

GROUP BY LOCATION.LOCATION_NAME, ITEM_TYPE.ITEM_TYPE_NAME;

IPEDS Eresources

SELECT BIB_TEXT.TITLE, BIB_TEXT.BIB_ID, MFHD_MASTER.DISPLAY_CALL_NO, ELINK_INDEX.LINK

FROM (BIB_MASTER INNER JOIN ((BIB_MFHD INNER JOIN BIB_TEXT ON BIB_MFHD.BIB_ID = BIB_TEXT.BIB_ID) INNER JOIN (MFHD_MASTER INNER JOIN LOCATION ON MFHD_MASTER.LOCATION_ID = LOCATION.LOCATION_ID) ON BIB_MFHD.MFHD_ID = MFHD_MASTER.MFHD_ID) ON BIB_MASTER.BIB_ID = BIB_TEXT.BIB_ID) INNER JOIN ELINK_INDEX ON BIB_TEXT.BIB_ID = ELINK_INDEX.RECORD_ID

WHERE (((BIB_MASTER.SUPPRESS_IN_OPAC)="N") AND ((MFHD_MASTER.LOCATION_ID)="51"))

ORDER BY BIB_TEXT.TITLE;

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

• Article last edited: 02-FEB-2022