How to get list of print book holdings in database?

Question

How can a list of print book holdings in Voyager be generated?

Answer

Query the database and export results to Excel. In the query below, "print holding" is determined by presence of ISBN. Query can be customized locally with a more refined set of parameters based on local MARC data.

1. Open Prepackaged Reports (Reports.mdb)
2. Create > Query Design > click Close in Show Table box
3. Click SQL View in menu bar.
4. Paste query into view (query only checks for presence or absence of ISBN; apply additional parameters as needed to suit local data):

SELECT MFHD_MASTER.DISPLAY_CALL_NO
, BIB_TEXT.TITLE
, LOCATION.LOCATION_NAME
, BIB_TEXT.NETWORK_NUMBER
,BIB_TEXT.ISBN
, BIB_TEXT.BIB_ID
FROM MFHD_MASTER
,LOCATION
,BIB_TEXT
,BIB_MFHD
WHERE MFHD_MASTER.LOCATION_ID = LOCATION.LOCATION_ID
and BIB_TEXT.BIB_ID = BIB_MFHD.BIB_ID
and BIB_MFHD.MFHD_ID=MFHD_MASTER.MFHD_ID
and BIB_TEXT.ISBN is not null
ORDER BY BIB_TEXT.TITLE
,BIB_TEXT.BIB_ID
,BIB_TEXT.ISBN;

5. (optional) Click Save to save the query and give it a name.
6. Click Run in menu bar.
7. Click External Data menu > Export: Excel
8. Give file name & format.

Additional Information

• For serials holdings, a similar query using BIB_TEXT.ISSN can be constructed.
• To add usage statistics tracked in CIRC_TRANSACTIONS and CIRC_TRANS_ARCHIVE, join MFHD_ITEM table to join the circulation transaction tables to the query. ITEM table can also be joined to include HISTORICAL_BROWSE total.

Example of joining ITEM to include HISTORICAL_BROWSE:

SELECT MFHD_MASTER.DISPLAY_CALL_NO
, BIB_TEXT.TITLE
, LOCATION.LOCATION_NAME
, BIB_TEXT.NETWORK_NUMBER
, BIB_TEXT.ISBN
, BIB_TEXT.BIB_ID
, ITEM.HISTORICAL_BROWSE
FROM MFHD_MASTER
,LOCATION
,BIB_TEXT
,BIB_MFHD
,MFHD_ITEM
,ITEM
WHERE MFHD_MASTER.LOCATION_ID = LOCATION.LOCATION_ID
and BIB_TEXT.BIB_ID = BIB_MFHD.BIB_ID
and BIB_MFHD.MFHD_ID=MFHD_MASTER.MFHD_ID
and BIB_MFHD.MFHD_ID=MFHD_ITEM.MFHD_ID
and MFHD_ITEM.ITEM_ID=ITEM.ITEM_ID
and BIB_TEXT.ISBN is not null
ORDER BY BIB_TEXT.TITLE
,BIB_TEXT.BIB_ID
,BIB_TEXT.ISBN;

• Article last edited: 06-Apr-2017