Monday, October 31, 2011 9:34 AM
Inmagic Genie has wonderful features for reporting on loan statistics. You can search the Loans module by date, borrower, department, call number and other key fields, and view the results grouped by these same fields to give you a count of loans for different items. You can also access the Statistics page to quickly view a count of overdue items.
But what if you want to view a list of all your books that have never been loaned? Perhaps these are candidates for weeding, to make room on the shelves for new or more popular items. In Genie, the Loans database is linked to the Catalog, Items and Borrowers databases, but as is the case with all DBText databases, the link is one-way. If a book has never been loaned, it won’t appear in the Loans database, so can’t be pulled up by a query, and a search in the Catalog can’t retrieve Loan records.
In the past few months we’ve had two clients approach us wondering how to generate a list of the loan-less books, and we’ve used the same solution in both cases. We’ve taken data from their Catalog and Loans databases, imported it into MS Access, and generated a report that summarizes loan activity by Title. The data includes the quantity of loans, including those with no loan, allowing the librarians to get a really good sense of which items in their collection are most borrowed, and which least. This type of report doesn’t really need to be in Genie, since it’s not something you’re likely to do very often – perhaps once per year at most.
You can do this yourself using the steps below, but please do contact us if you’d like us to do it for you.
- Use DBTextWorks to export data from the Genie Catalog and Loans databases to ASCII text (tab-delimited is recommended).
- Import each file into a separate table in MS Access.
- In Access, create a query to join the two tables, with a link from CatID in the Catalog table to LoanCatID in the Loans table.
- Be sure the join is set to include all records from the Catalog (critical to counting records with zero loans).
- Include in the query the CatID, CatTitle, CatCallNumber and other Catalog fields you wish to view in the report, as well as the LoanID field.
- Adjust the query to group by all fields, sort by Call Number, Title or other fields, and count the LoanID field (see screenshot for details).
You could optionally export the query results to Excel to review or share with those unfamiliar with Access.