Shared SQL for Reports in Voyager

In addition to Voyager’s Prepackaged Access Reports, specialized SQL queries can also be created in Access to produce specialty reports. This page collects SQL queries created by CARLI staff and I-Share member libraries to share with the greater I-Share community. The SQL provided here can simply be copied and pasted into a new MS Access query.

How to submit a query to share on this page

How to use shared queries

If you have any questions or comments about SQL sharing, please contact the CARLI Office at support@carli.illinois.edu

Categories of Shared SQL

Acquisitions

Funds and ledgers
Purchase Orders and Line Items
Invoices
Other

Cataloging Activity and Statistics

Description of a Library’s Collection

Local Catalog Maintenance

Bibliographic Records
MFHD Records
Item Records

Local Circulation Maintenance

Local Circulation Statistics

Managing your Access Queries

Reserves

Serials

System Administration

Universal Borrowing

What is needed to run Access Reports in Voyager

Getting started with Access Reports in the I-Share environment posted 1/6/2009

Oracle 10g/ODBC Installation materials

Updating the Character Set for Oracle 10g 4/15/2008 If you are finding data with diacritics displaying as Error(12001) in MS Access, follow these instructions to update your character set.

CARLI 2009 Version of the reports.mdb file for MSAccess NEW posted 12/4/2009

Features of the CARLI_reports_2009.mdb file for MSAccess NEW posted 12/4/2009
Includes information on the 2009 Conspectus queries.

"Copying" Queries from One .mdb File to Another NEW posted 12/2/2009

Linking the CARLI_reports_2009.mdb file NEW posted 12/4/2009

Resources for Report Writing

Voyager Data Dictionary

REVISED 12/2/2009
The Voyager Data Dictionary for V7.1 includes "Tricks for Using Voyager Tables in Queries". App. A has information about the “Type mismatch in expression” message, and App. B has details about what’s in the LINK and LINK_TEXT fields of ELINK_INDEX.

Reports with Call Number Ranges: How to Request Them and How to Write Them posted 01.31.2006

Historical Browse Statistics in I-Share posted 02.18.2009
A browse is counted in Voyager when an item that is not charged out is discharged. CARLI is sampling the browses periodically and making it possible to run Access queries on the samples. This document describes the set up needed to run the Access queries and the quirks of interpreting the results.

Oracle Indexes for Voyager Tables rev. for V6.1 06.09.2006

Queries to Demonstrate the Date Time Functions in Access posted 9.27.2005

A Strategy for Using BLOB Functions on Large Databases posted9.27.2005

Advanced Report Writing: Outer Joins, the BLOB Functions, and Subqueries & Make Table Queries 05.11.2006
This PowerPoint presentation was given by Cathy Salika at the Reports Forum on May 11, 2006.

Alternatives to the BLOB Queries rev. for V6.1 06.15.2006

Updating the Character Set for Oracle 10g 4/15/2008 If you are finding data with diacritics displaying as Error(12001) in MS Access, follow these instructions to update your character set.

Class Diagrams (formerly known as Entity-Relationship Diagrams) for Version 6.1 and higher

Each class diagram shows a set of Voyager tables that work together to perform some function. The arrows between them show how the tables relate, in other words, how the tables should be joined in your queries. The ExLibris set of class diagrams are available on SupportWeb. Their diagrams have page numbers 1 through 30. CARLI has created some additional diagrams which are available below and numbered pages 31 to 39.

Class Diagrams for Bibs

Class Diagrams for Holdings

Class Diagrams for Authorities

Class Diagrams for Acquisitions Setup

Class Master Set-up

Class Diagrams for Cataloging Setup

Class Diagrams for Circulation Setup

Class Diagrams for Vendors (ppt)

Class Diagrams for Elinks (URLs)

Class Diagrams for Complex Patterns