Targeting Dust Bunnies: Using Access Reports for Collection Management

1
Targeting Dust Bunnies Using Access Reports
for Collection Management

• Donna Smith and Barb Loudy
• Kankakee Community College
• CARLI Reports Wimba Session
• February 26, 2009

2
Deciding what to weed (other than condition and
duplication) can be difficult. This presentation
highlights standard and custom ACCESS queries
related to targeted collection analysis.
Variations of selection criteria and reasons for
use.
3
Intentional Weeding since FY2002 to decrease the
age of the collection

• 2008 2001
• Pre 1970s 19 (from 34)
• 1970s 14 (from 20)
• 1980s 24 (from 29)
• 1990s 22 (from 16)
• 2000s 21 (from lt1)
• 42,000 49,000

4
Pluses of the plan

• Promotes good relations with faculty
• Saves time choosing materials through targeted
  lists of materials for review
• Removes very old and unused materials
• Highlights newer materials
• Cleans the dust-bunnies off the shelves

5
The first targetUncirculated Titles
SELECT LOCATION.LOCATION_NAME, MFHD_MASTER.DISPLAY
_CALL_NO, MFHD_ITEM.ITEM_ENUM, ITEM_BARCODE.ITEM_B
ARCODE, BIB_TEXT.TITLE_BRIEF, BIB_TEXT.PUBLISHER_D
ATE FROM (((((ITEM INNER JOIN MFHD_ITEM ON
ITEM.ITEM_ID MFHD_ITEM.ITEM_ID) INNER JOIN
MFHD_MASTER ON MFHD_ITEM.MFHD_ID
MFHD_MASTER.MFHD_ID) INNER JOIN BIB_MFHD ON
MFHD_MASTER.MFHD_ID BIB_MFHD.MFHD_ID) INNER
JOIN BIB_TEXT ON BIB_MFHD.BIB_ID
BIB_TEXT.BIB_ID) INNER JOIN LOCATION ON
MFHD_MASTER.LOCATION_ID LOCATION.LOCATION_ID)
INNER JOIN ITEM_BARCODE ON ITEM.ITEM_ID
ITEM_BARCODE.ITEM_ID WHERE (((ITEM.HISTORICAL_CHAR
GES)"0") AND ((ITEM.HISTORICAL_BROWSES)"0")) ORD
ER BY LOCATION.LOCATION_NAME, MFHD_MASTER.DISPLAY_
CALL_NO
6
Which dust bunnies?

• Uncirculated Item Report
• Built by ILCSO
• Displayes Location, Call , enumeration,
  barcode, title, publisher date
• If No historical charges AND No historical
  browses
• For all locations in the LRC
• Can be used for weeding or identification of
  materials to move to storage

7
Immediate changes

• Result 70 of the collection listed
• Added call - focus on 12-13 program review
  areas per year. (2-3 per month)
• Added date published criteria prior to 1970
• Decided to not pull multivolume sets
• Excel output edited by librarian - pull list
  done by students forwarded to faculty
• Started using additional CARLI/Voyager access
  reports

8
Analysis using Standard Access Reports

• Bibs by Pub Decade and Country
• Circulation Transactions Titles by date range
  with minimum
• Circulation Transactions Titles by minimum
  charges
• Item status lost, missing, damaged
• Last charged range June 2002 to xxxx

9
Changes Use

• Bibs by Pub Decade and Country
• Removed the sort by country
• Used to determine average age of entire
  collection
• Could add selection by call number range for more
  specific analysis
• Circulation Transactions Titles by date range
  with minimum OR
• Circulation Transactions Titles by minimum
  Charges
• ID high circulating items for possible
  replacement (particularly VHS to DVD)
• Added selection by call number and/or selection
  by location
• ID low circulating items (1 or 2) and publisher
  date for possible pulls
• HAVING (((LOCATION.LOCATION_NAME) Like "Video")
  AND ((Count(CIRC_TRANS_ARCHIVE.CHARGE_DATE))gtCInt
  (Minimum Charges)))

10

• Item status lost, missing, damaged
• Add call number range to identify items which
  should be analyzed as part of a review year
• Add historical charges browses to determine use
• Used to decide on replacement or deleting the
  item
• Last charged range June 2002 to xxxx
• Add call number range to identify items which
  might be pulled
• Initial focus on items with 2002 last charged
  dates

11
Each Year Variations on a theme for
Uncirculated Titles

• Round two targets
• Circ and browse counts of zero
• Published prior to 1970
• Third round more complex
• Last charged date prior to five years ago
• Circ count less than five
• Titles published prior to xxx date showing browse
  and circ counts

12
Improving Aim Customizing Uncirculated Titles

• Sample of customizations each is from a
  separate report
• WHERE (((BIB_TEXT.PUBLISHER_DATE) lt"1980")
• WHERE (((MFHD_MASTER.DISPLAY_CALL_NO) Like "QA")
• WHERE (((MFHD_MASTER.DISPLAY_CALL_NO) Like "RB")
  AND ((BIB_TEXT.PUBLISHER_DATE)lt"1995")) OR
  (((MFHD_MASTER.DISPLAY_CALL_NO) Like "RC")) OR
  (((MFHD_MASTER.DISPLAY_CALL_NO) Like "RG")) OR
  (((MFHD_MASTER.DISPLAY_CALL_NO) Like "RT")) OR
  (((MFHD_MASTER.DISPLAY_CALL_NO) Like "RX"))
• WHERE (((LOCATION.LOCATION_ID) Like "18") AND
  ((ITEM.HISTORICAL_CHARGES)lt"5") AND
  ((ITEM.HISTORICAL_BROWSES)lt"5"))
• WHERE (((MFHD_MASTER.DISPLAY_CALL_NO) Like
  "Q-R") AND ((BIB_TEXT.PUBLISHER_DATE) Between
  "1980" And "1995"))

13
Circ transactions minimum charges discharge lt
2003

• SELECT MFHD_ITEM.ITEM_ENUM, MFHD_ITEM.CHRON,
  LOCATION.LOCATION_NAME, Count(CIRC_TRANS_ARCHIVE.D
  ISCHARGE_DATE) AS CountOfDISCHARGE_DATE, Minimum
  Charges AS MinCharges, Bib Text.TITLE, Mfhd
  Master.DISPLAY_CALL_NO, Mfhd Master.NORMALIZED_
  CALL_NO
• FROM (LOCATION INNER JOIN Mfhd Master ON
  LOCATION.LOCATION_ID Mfhd Master.LOCATION_ID)
  INNER JOIN (Bib Text INNER JOIN
  (CIRC_TRANS_ARCHIVE INNER JOIN (BIB_MFHD INNER
  JOIN MFHD_ITEM ON BIB_MFHD.MFHD_ID
  MFHD_ITEM.MFHD_ID) ON CIRC_TRANS_ARCHIVE.ITEM_ID
  MFHD_ITEM.ITEM_ID) ON Bib Text.BIB_ID
  BIB_MFHD.BIB_ID) ON Mfhd Master.MFHD_ID
  BIB_MFHD.MFHD_ID
• GROUP BY MFHD_ITEM.ITEM_ENUM, MFHD_ITEM.CHRON,
  LOCATION.LOCATION_NAME, Minimum Charges, Bib
  Text.TITLE, Mfhd Master.DISPLAY_CALL_NO, Mfhd
  Master.NORMALIZED_CALL_NO
• HAVING (((Count(CIRC_TRANS_ARCHIVE.DISCHARGE_DATE)
  )lt"2003"))

14
UnCirculated to Circulated titles with multiple
selection criteria

• SELECT LOCATION.LOCATION_NAME, MFHD_MASTER.DISPLAY
  _CALL_NO, MFHD_ITEM.ITEM_ENUM, ITEM_BARCODE.ITEM_B
  ARCODE, BIB_TEXT.TITLE_BRIEF, BIB_TEXT.PUBLISHER_D
  ATE, ITEM.HISTORICAL_BROWSES, ITEM.ITEM_TYPE_ID,
  ITEM.HISTORICAL_CHARGES, CIRC_TRANS_ARCHIVE.DISCHA
  RGE_DATE, CIRC_TRANS_ARCHIVE.DISCHARGE_DATE
• FROM CIRC_TRANS_ARCHIVE INNER JOIN ((((((ITEM
  INNER JOIN MFHD_ITEM ON ITEM.ITEM_ID
  MFHD_ITEM.ITEM_ID) INNER JOIN MFHD_MASTER ON
  MFHD_ITEM.MFHD_ID MFHD_MASTER.MFHD_ID) INNER
  JOIN BIB_MFHD ON MFHD_MASTER.MFHD_ID
  BIB_MFHD.MFHD_ID) INNER JOIN BIB_TEXT ON
  BIB_MFHD.BIB_ID BIB_TEXT.BIB_ID) INNER JOIN
  LOCATION ON MFHD_MASTER.LOCATION_ID
  LOCATION.LOCATION_ID) INNER JOIN ITEM_BARCODE ON
  ITEM.ITEM_ID ITEM_BARCODE.ITEM_ID) ON
  CIRC_TRANS_ARCHIVE.DB_ID MFHD_ITEM.ITEM_ID
• WHERE (((MFHD_MASTER.DISPLAY_CALL_NO) Like
  "BF") AND ((BIB_TEXT.PUBLISHER_DATE)lt"1980")
  AND ((ITEM.HISTORICAL_BROWSES)lt"5") AND
  ((ITEM.HISTORICAL_CHARGES)lt"5") AND
  ((CIRC_TRANS_ARCHIVE.DISCHARGE_DATE)lt"2003"))
• ORDER BY LOCATION.LOCATION_NAME,
  MFHD_MASTER.DISPLAY_CALL_NO

15
Getting Started

• Is there something that already exists that is
  somewhat similar to what you want?
• Voyager/CARLI access reports via Access or
  documented in the Voyager manual
• CARLI shared SQL (loginlibrary barcode)
  http//www.carli.illinois.edu/I-Share/secure/sql/
  
• Something new OR Voyager server report?
• CARLI work requests online (WRO)
• Definitions http//www.carli.illinois.edu/mem-se
  rv/WROtablefinal.pdf
• Submit Request https//ilcso.illinois.edu/wro/cg
  i/wro.cgi

16
Tips

• Save as to a new appropriate name before
  modifying
• Do the output numbers look reasonable? For
  instance uncirculated items in U.S. history
  should be greater than 10
• Support_at_CARLI can help figure out where
  statements
• CARLI reports sessions on Friday, Feb 27-
  refreshers dont hurt!

17
Questions?

• Barb Loudy
• Circulation Supervisor
• bloudy_at_kcc.edu
• Donna Smith
• Director, Instructional Technology, Faculty
  Development and the Learning Resource Center
• dsmith_at_kcc.edu