Blog

Regular Expressions in MS Access

July 9th, 2010

I spend most of my time writing SQL queries for Oracle and I've gotten used to functions like REGEXP_LIKE. Lately, however, I have been training a work-study student how to use MS Access for reporting and no such function seemed to exist. I generally try to shy away from delving into advanced MS Access features, but my works-study needs regular expressions and I want my work-study to learn. So, I scrounged the internet and came up with this.

Setting Item Type and Location Used for WorldCat Navigator (NRE) Items in Voyager

March 25th, 2010

WorldCat Navigator integrates with Voyager and creates items as they are received. It does this by making a Create Bib Request to the SIP server, which creates not only a bib, but also a MFHD and an item record. The item type and location associated with these newly created records is determined by the location used to sign in to the SIP server. I hope to explain how that item type and location is determined.

Circulation Counts for Unique Patrons On Any Given Day

February 24th, 2010

There was a request today for a Voyager report on a listserv. Here it is.

I'm trying to compare library usage from Fall 08 semester to Fall 09 semeseter. One of the things I'd like to do is show how many patrons checked items out of the library during that time period.

  1. If the same patron checked items out on 5 DIFFERENT days during the month of January, I'd like to count that patron 5 times.
  2. If the same patron checked out multiple items on the SAME day, I'd like to count that patron once for that day.

Does anyone have an access report that they use to show circulation usage that they could share?

I whipped up a quick report, but it doesn't run in MS Access. Instead, you must use sqlplus or any other Oracle driver. You can find the most up to date sql for it on GitHub. However, this is what I have as of this morning:

Automate Deleting SSNs using Macro Express where Pptrnupdt Doesn't Work

February 23rd, 2010

Several months ago I purged most of the social security numbers from Healey Library's patron database using Voyager's patron update (Pptrnupdt), but there were several thousand patrons remaining without institution ids. Pptrnupdt only matches patrons by social security number or by Institution ID. Therefore Pptrnupdt would not work gracefully to finish off the project. If anyone from Ex Libris is reading this, please include a Patron ID index type, thanks.

SQL to Generate User Lists in Voyager, Drupal, and ILLiad

January 12th, 2010

Today, we need reports of all active users for our applications. Here, I whip up SQL to retrieve this data in Voyager, Drupal, and ILLiad.

I find most interesting that each application has a unique approach to staff accounts. Both Voyager and ILLiad have a distinct table for its patrons and staff. Drupal makes no distinction. This makes sense in terms of design intentions. Voyager and ILLiad are products with an obvious distinction between customer and employee. Drupal can implement the same effect, but comes as a more generic tool.
Syndicate content