Querying Voyager with MS Access

1
Querying Voyager with MS Access
10 March 2004
Alan Manifold Systems Implementation
Manager Purdue University Libraries manifold_at_purdu
e.edu
2
QUERIES

• Queries have lots of functions
• Select specific records
• Books with no authors
• Select specific fields
• Author, title, publisher
• Join information from multiple tables
• Patron name, group and barcode
• Group or sort records
• Count of books by location
• Any combination of the above

3
AN ACCESS QUERY
Title and Author of books that have no call
numbers, were created before last July, and are
not suppressed.
4
CREATING A QUERY
Use Design View
The Simple Query Wizard is not helpful
5
SELECT YOUR TABLES
The first step is to scroll through the list of
tables and Add the ones you want in your query.
6
WHICH TABLES TO CHOOSE?

• Three reasons to add a table to a query
• To select a field from it
• To apply criteria to a field
• To link other tables together

7
CHECK THE JOINS
This time, the tables all linked themselves, but
they dont always. Create any needed links.
8
SELECT YOUR FIELDS
Drag and drop fields from top to bottom or
double-click a field to send it to the bottom.
9
REASONS TO ADD A FIELD
Or any combination of the three
To include it in the results To apply criteria
to it To affect grouping or sorting
10
APPLY CRITERIA
Type each criterion on the Criteria line under
the field to which it applies.
11
SHARING QUERIES WITH OTHERS, INBOUND
12
SHARING QUERIES WITH OTHERS, INBOUND
13
SHARING QUERIES WITH OTHERS, INBOUND
14
SHARING QUERIES WITH OTHERS, INBOUND
15
SHARING QUERIES WITH OTHERS, INBOUND
16
SHARING QUERIES WITH OTHERS, INBOUND
17
SHARING QUERIES WITH OTHERS, OUTBOUND
SQL View
18
SHARING QUERIES WITH OTHERS, OUTBOUND
SQL View
Copy
Paste into e-mail or text file
19
DONT BE A LOSER!
Once a query has started, you have to wait for it
to end or use the Task Manager to End Task,
which will lose work in progress.
SAVE QUERIES BEFORE RUNNING THEM!
20
HOW DO QUERIES WORK?
We dont have to know how the query is executed,
but we need to understand the concepts.
21
HOW DO QUERIES WORK?
These are the general steps in a query
Join tables Apply criteria Select fields Sort
results
For this query Join BIB_TEXT to BIB_MFHD Join
that result to MFHD_MASTER Discard suppressed
records, records with call numbers and recent
records Select TITLE and AUTHOR fields No sorting
was requested
22
JOIN BIB_TEXT TO BIB_MFHD

1365303 1511347 . . . 1365304 1511348 . .
. 1365305 1511349 . . . 1365309 1511353 . .
. 1365311 1511354 . . . 1365312 1511356 . .
. 1365314 1511360 . . . 1365314 1512724 . . .

1365315 1511361 . . . 1365316 1511362 . .
. 1365317 1511363 . . . 1365318 1511364 . .
. 1365319 1511371 . . . 1365320 1511372 . . .
23
JOIN RESULT TO MFHD_MASTER

Same number of records, more fields
1365303 1511347 . . . 1365304 1511348 . .
. 1365305 1511349 . . . 1365309 1511353 . .
. 1365311 1511354 . . . 1365312 1511356 . .
. 1365314 1511360 . . . 1365314 1512724 . . .

1365315 1511361 . . . 1365316 1511362 . .
. 1365317 1511363 . . . 1365318 1511364 . .
. 1365319 1511371 . . . 1365320 1511372 . . .
24
WHAT DOES THE QUERY DO?
Final Join
25
GETTING STARTED
DONT WORK IN THIS FOLDER!
You already have an Access database on your
workstation. Copy the file, c\Voyager\Access
Reports\reports.mdb to another folder and do all
your work there.
26
QUERY CREATION STEPS
You now know all of the steps in creating
queries

• On Queries tab, click New
• Select Design View
• Select appropriate tables
• Check joins
• Select fields
• Apply criteria
• Save the query before running it

27
MORE ABOUT CRITERIA
By applying the right criteria, you can be more
selective about the records that are returned.
28
USING CRITERIA EFFECTIVELY
A plain value implies equals.
ugrl,resv Text constant (case
sensitive) 83456 Numeric constant
(Text) 3/10/2004 Date constant (be careful)
Criteria with operators
Gift Not equal Not Approved Not equal
1/1/2004 Greater than
10000 Greater than or equal to than or equal to
29
PATTERN MATCHING
For text fields, you can use Like and an
asterisk to do pattern matching. An asterisk
() matches any sequence of zero or more
characters.
Criterion
Matches
main, maintain, maintenance, mainframe,
main street, refe, ugrl,refe,
phys,refe, her, hiker, homer,
helicopter, his mother, ser, serial,
loser, reserves, alternative service
Like main Like refe Like her Like
ser
30
MULTIPLE CRITERIA
You can use comparison and Boolean operators to
apply multiple criteria to a field
5 And 100 math And Not
math,peri 2/1/2004 And 3/1/2004 Between 5 And 8 Missing Or
LostSystem Applied Or LostLibrary
Applied Missing Or Like Lost ugrl Or
hsse Or mgmt engr,refd Or engr,refe
31
USING DATES AS CRITERIA
Date constants Use number signs
2/1/2004 Affected by Regional
Settings Defaults to current year 2/1
2/1/2004 Year alone is not enough
2003 Date/time problem All Date/Time values
include a time -- 2/1/2004 equals midnight at
the beginning of the day, 2/1/2004 Ranges Betw
een 3/10/2004 And 3/11/2004 Between
3/10/2004 And 3/10/2004 235959
32
EXPRESSIONS
An expression, or calculated field, takes the
values of one or more fields and uses them to
create new values.
ÖW
X Y
ROUND(N)
UCASE(NAME)
ABS(ALLOCATION-EXPENDITURE)
I - (K L)
33
EXPRESSIONS IN FIELDS
Wherever you can use a field, you can use an
expression
Change the field name (before the colon) from
Expr1 to something meaningful
34
SOME USEFUL FUNCTIONS
CCur Convert to currency CCur(AMOUNT) CCur(53.9)
53.90 Val Numeric value of text Val(MFHD_COUNT)
Val(13) 13 UCase Convert to upper
case UCase(LAST_NAME) UCase(Lafayette, Ind.)
LAFAYETTE, IND. LCase Convert to lower
case LCase(LAST_NAME) LCase(West Lafayette,
Ind.) west lafayette, ind. Left Left part of
text Left(CALLNUM,3) Left(004.12 A324d,3)
004 Right Right part of text Right(CALLNUM,3) R
ight(004.12 A324d,3) 24d Mid Part of
text Mid(CALLNUM,5,2) Mid(004.12 A324d,5,4)
12 A Date Current date Date() Date()
3/10/2004
35
MORE INFO ON FUNCTIONS
Help is available on functions in at least two
ways. Check Help Contents.
36
EXPRESSIONS IN THE WILD
37
MONETARY VALUES

• Monetary values problems
• Converted to text
• Integers, stored in cents

To include a monetary figure in a
query CCur(AMOUNT/100)
38
PROMPTING FOR CRITERIA
Left square bracket Prompt Right square
bracket Example Instructor last name?
SYNTAX
39
USING PROMPTS
Make your prompts the best they can be. Imagine
that the users do not know what kind of
information is expected. Provide enough
information in the prompt that they can figure it
out.
Start Date First date for report Starting
date for report (dd/mm/yyyy)
Dont use prompts like start val
40
REPEATED PROMPTS
If you use the same exact prompt more than once,
it only prompts you once and substitutes the same
value in each spot
You can use this to save the users choice as a
separate field
41
AGGREGATING DATA
Agriculture SUM 1,569.11 AVG 174.35 MIN
13.44 MAX 834.56 COUNT 9 STDEV
255.154231108907 VAR 65103.6816527778 FIRST
62.50 LAST 43.45
LEDGER AMT Agriculture 62.50 Agriculture
122.04 Agriculture 13.44 Agriculture
88.85 Agriculture 233.31 Agriculture
78.54 Agriculture 834.56 Agriculture
92.42 Agriculture 43.45 Biology
34.22 Biology 23.69 Biology 54.64 Biology
100.32 Biology 103.54 Biology 75.25
Biology SUM 391.66 AVG 65.28 MIN
23.69 MAX 103.54 COUNT 6, etc.
42
AGGREGATE FUNCTIONS
Click the sigma (S) to get a Total line.
43
AGGREGATE FUNCTIONSGROUP BY
Wherever the values are equal, collapse into a
single record. If you group by multiple fields,
they collapse only where all are equal.
44
AGGREGATE FUNCTIONSTHE FUNCTIONS
Sum Avg Min Max Count StDev Var First Last
Wherever the Group By fields are equal, perform
the selected operation on the fields and
substitute the results.
45
AGGREGATE FUNCTIONSSUM
Know the difference between Count and Sum Count
counts and Sum adds.
46
VOYAGER TABLE ROADMAP
Media Scheduling
SysAdmin
Circulation
Acquisitions
Citation Server
OPAC
Cataloging
KEY VOYAGER TABLE RELATIONSHIPS ARE COMPLEX
47
CORE RELATIONSHIPS
48
CIRCULATION RELATIONSHIPS
CALL SLIPS
BIB_TEXT
PROXY_PATRON
COURSE RESERVES STUFF
PATRON_GROUP
LOCATION
BIB_MFHD
PATRON_BARCODE
MFHD_MASTER
CIRC_TRANSACTIONS (and RENEW_TRANSACTIONS)
PATRON
ITEM
MFHD_ITEM
CIRC_TRANS_ARCHIVE (and RENEW_TRANS_ARCHIVE)
MEDIA
ITEM_TYPE
PATRON_STATS
FINES FEES
ITEM_STATUS
ITEM_STATUS_TYPE
ITEM_BARCODE
SHORT LOANS
PATRON_STAT_CODE
ITEM_STATS
ITEM_STAT_CODE
HOLDS/RECALLS
49
GETTING ALL THECIRCULATION HISTORY
There is a record in CIRC_TRANSACTIONS for each
item that is currently checked out
CIRC_TRANSACTIONS
RENEW_TRANSACTIONS
There is a record in CIRC_TRANS_ARCHIVE for each
item that used to be checked out, but has been
returned.
CIRC_TRANS_ARCHIVE
RENEW_TRANS_ARCHIVE
To get all the transactions for any given period,
you need data from both tables. There are two
similar tables for renewal data.
50
UNION QUERIES
CHOOSE Query-- SQL Specific-- Union
When you have information in two tables and want
to make them into a single table, use a Union All
Query.
The two tables (or the fields you select from
them) must have the exact same fields.
51
UNION QUERIES
The asterisk operator, which stands for All
fields from the table, makes it easy to create a
Union All from two Design View queries.
52
CIRCCHARGES_VW
CIRCCHARGES_VW performs the UNION between
CIRC_TRANSACTIONS and CIRC_TRANS_ARCHIVE (and
more!).
53
TABLE BASICS ACQUISITIONS
LINE_ITEM_FUNDS funds assigned to PO line items
in Copies/Funds box (amounts in foreign
currency) PO_FUNDS funds assigned to PO line
items, plus funds for PO adjustments until
invoice is approved (base currency) LINE_ITEM_COPY
funds assigned to line item copies on the PO
(no amounts) FUND_PAYMENT funds used for
prepayments and invoice adjustments
(foreign) INVOICE_LINE_ITEM_FUNDS funds assigned
or used for invoice line items (foreign) INVOICE_F
UNDS funds assigned or used for invoice line
items, plus funds for invoice adjustments, after
approval (base)
54
FUNDS IN VOYAGER
55
CATALOGING STATISTICS
You may want statistics on records cataloged by
operator, location or call number. The key to
all of these is determining when an item was
cataloged. This depends on your workflow.
For bibs and MFHDs, you should be able to
find the create or modify date with a query
that has something like this, but it doesnt
work. Instead, use
56
VOYAGER LOCATIONS
There are three locations in Voyager. You can
pretty much ignore the MFHD.
57
CIRCULATION LOCATIONS
Circulation always uses item temp location if
present or perm location if not.
Read item barcode
Find item record for this barcode
Does item have Temporary Location?
Set Governing Location to Perm Location
NO
YES
Get circulation policy group for Governing
Location
Set Governing Location to Temp Location
58
OPAC LOCATIONS
WebVoyage displays the MFHD location for some odd
situations, but usually displays the item
locations. Limiting by location uses the MFHD
location.
59
HELP!!!!!
Creating reports is a big part of my job. If you
run into problems while developing queries or
reports, you should always feel free to contact
me with questions.
manifold_at_purdue.edu 49-42884
60
EVALUATIONS
Please fill out online evaluations for this class
http//www.lib.purdue.edu/staffdev
Thank you for participating!