Transcript ACCESS REPORTS FOR VOYAGER

SCARY QUERIES LAID TO REST
Getting Started with Voyager
Prepackaged Access Reports
Presented by Jean Vik, Associate Library Director
The University of Texas at Dallas
McDermott Library
ELSUG October 2008
Overview
 PC
Setup
 Microsoft Access
 Prepackaged Reports
Why MS Access
 Create
queries & reports using a graphical
interface
 Prepackaged reports installed with clients
– C:\Voyager\Access Reports\Reports.mdb
 Customize
prepackaged reports & queries
 Design new reports & queries
Microsoft Access
 Must
have Microsoft Access installed
 Install reports.mdb
– Administrator privileges on PC a must
– Part of Voyager client installation
– Instructions found in Voyager® 6.5 Reporter
User’s Guide (p. 1_17)
– These reports are not run through Reporter
PC Setup
 Install
–
–
–
–
and configure ODBC drivers
Open Database Connectivity
Must match version of Oracle on server
Download from Ex Libris Support
Allows MS Access to communicate directly
with the Voyager database
 Uninstall
old version of ODBC before
installing new one*
*See Voyager® Reporter User’s Guide for complete instructions.
Building Links to Tables
 Configure
database links
– Go to C:\Voyager\Access Reports and click on
the Reports.mdb file
– Access will open and reports: Database dialog
box opens
u
u
u
a. Click the Forms button.
b. Select Build database links to Voyager.
c. Click the Open button.
Building Links to Tables in
Access 2003
 Access
2003
 Forms Button
Building Links to Tables in
Access 2007
 Access
2007
– Click down arrow
for menu
– Choose Forms to
get to this view
Building Links to Tables
 The
first time the
tables will fail to
link
 The word error!
appears at the end
of each table tried
 Stops trying to link
Building Links to Tables
 In
earlier versions
of Access choose
 File>Get External
Data> Link Tables
ReLinking Tables
 Office
2007
– Ribbon
– Database Tools
– Linked Table
Manager
– Select tables to be
updated
 Copy
Reports.mdb
file to other pc’s
MS Access Security
Set Security Level to
“Low” in 2003
 Access 2007 has
added security
 Add path to your
Reports.mdb to the
Trust Center to avoid
this Security Alert
 Click “Enable this
content” to allow for
one session

See “oracle9i_clientODBC.pdf for
complete installation instructions.
Tables/Queries/Reports
 The
down arrow on the left navigation pane
allows switching between different
categories:
– Tables: show the linked Oracle tables
– Queries: add specific criteria & build new
queries
– Reports: built on top of a query to format data
for printing
Navigation Pane
Access Query
 Right
click a query and choose design view
to see the tables involved and how they are
related
Query Result
After running the query the results display in
tabular form
 Export to Excel to create a quick report

Data in Excel
 Remove

Sort
columns not needed in the report
Access Report
Running the report associated with this query
produces the same data nicely formatted for printing
 Cannot modify layout

Prepackaged Report Highlights
All prepackaged reports came from enhancement
requests
 Limit results by adding Criteria

Prepackaged Query Highlights

Acq: issues received by component and date range.
This query uses a view table.
Creating Custom Queries
First, make sure your report is not one of the
prepackaged reports!
 Decide which data elements should be included in
the report.
 Use the database dictionary to determine which
tables contain the required data.
 Create a new query in Access by adding these
tables. Other tables may be required for linking
purposes.
 Add the data elements from the tables and any
criteria or restrictions.

Rules of Thumb
 Start
small and save your queries
frequently.
 Make a copy of a query that has some of the
elements you want & give it a new name.
 First create the ‘core’ of your query, save it,
then run it as a test.
Rules of Thumb
 Looking
at the prepackaged queries in
design view is a good way to learn.
 With practice, you will come to know the
Voyager data structure and need to consult
the printed tools less frequently.
 Examine query results with a critical eye.
Example 1: Circulation asks for a
list of Community User Patrons
 What
u
data elements should be included?
Last name, first name, barcode, phone number
 Is
there a query that provides this
information?
 Check the Data Dictionary to determine
which tables to use
– Voyager® 6.5 Technical User’s Guide Appendix A
List of Community User Patrons
 What data elements should be in the
Last name, first name, barcode, phone number
query?
 Look
at tables beginning with PATRON to
find where this stuff lives.
– The PATRON table contains the LAST_NAME &
FIRST_NAME.
– The PATRON_BARCODE table contains
PATRON_BARCODE.
– The PATRON_PHONE table contains
PHONE_NUMBER.
Community User Patrons

We also need to include PATRON_ADDRESS for
linking purposes and PATRON_GROUP so we can
just limit to Community User.
Community User Patrons

To add data, simply double click the elements in
the tables.
Note: Criteria in Access are case sensitive.
Example 2: List Vendors
Ordered from this FY
 Data
to include: Vendor name, order date
 Check the Data Dictionary to see which tables
to use
– The VENDOR table contains VENDOR_NAME.
– The PURCHASE_ORDER table contains
PO_CREATE_DATE.
List of Vendors
Note: Enter criteria >9/1/2007 then Tab & Access adds correct formatting.
List of Vendors
 Get
a count of orders placed with vendors
between a range of dates specified at the
time the query is run & sort the vendor
names alphabetically.
 The Totals function (the ∑ button) allows
the use of aggregate functions like “count”
 Use the Sort dropdown to alphabetize by
Vendor name
Count of Orders by Vendor
Example 3: Shelf List by Location
 Data
to include: call number, title,
enumeration/chronology, barcode.
 Sort by call # and then enumeration.
 Tables used for bibliographic queries:
BIB_TEXT, BIB_MASTER, BIB_MFHD,
MFHD_MASTER, MFHD_ITEM, ITEM,
LOCATION, ITEM_BARCODE
How are these tables related?
Note: There are two intermediate tables for linking purposes: BIB_ MFHD
and MFHD_ITEM. MFHD_ITEM can also be used to extract enumeration
and chronology.
Shelf List by Location
Include both the NORMALIZED_CALL_NO field and the DISPLAY_CALL_NO
Field to get a correct sort of the Call Numbers
Voyager-L Reports
 Email
includes the SQL required for a
report
 Copy everything and paste it in a SQL view
 Change the view to Design View to see
which tables are used
 Modify criteria to match your local
locations, etc.
Questions?
 Thank
you and have a Happy Halloween!