What

1
Whats the Worst that could Happen?Trouble-Shooti
ng Your Voyager Queries
ELSUG October 8, 2009 Cathy SalikaCARLI
2
Will this query tell me how many items I have in
each location?
3
Moral Always remove extra tables from your
queries.
4
Will this query tell me how many MFHDs I have in
each location?
5
(No Transcript)
6
Null is a special value
This library has 14,470 MFHDs
0 MFHDs 13,071 MFHDs
7
The missing MFHDs have a value of Null
1,399 MFHDs
8
Null is
different from blank different from
zero Null means that the value has not been
filled in.
And null fields are not counted with the Count
function!
9
Moral Never Count a field that might have a null
value in it.
The _ID fields usually do not have a null value.
10
Null is a special value
This library has 14,470 MFHDs
0 MFHDs 13,071 MFHDs
11
(No Transcript)
12
Moral Use simple queries to check the results of
complicated ones.
13
Will this count the items that circulated during
2007?
14
Moral Dates default to 1200 a.m. unless you
specify a time.
In criteria, you usually have to specify the day
after the last date that you want Between
1/1/2007 And 1/1/2008
15
Moral You need to know whats in the tables.
When an item is discharged, the circ transaction
moves from CIRC_TRANSACTIONS to
CIRC_TRANS_ARCHIVE.
16
How am I supposed to know that for every table???
The Data Dictionary! CARLIs version of the Data
Dictionary is available on EL Commons.
17
CIRC_TRANS Tables Circulation transactions are
recorded in CIRC_TRANSACTIONS until the item is
discharged, after which they are moved to
CIRC_TRANS_ARCHIVE. Consequently, the discharge
fields in CIRC_TRANSACTIONS are always blank.
  When a transaction is archived, the value of
circ_transaction_id is changed. In both tables,
circ_transaction_id is assigned sequentially as a
record is added.    For many circ statistics, you
will want to combine data from these two tables.
Endeavor provides an Access query called
Circulation Transactions (Charges) which does
this for you. From an Add Tables window in
Access, click the Queries tab and youll find
it.   The charge_type and discharge_type fields
have 2 values, N for Normal and O for Override.  
18
PATRON_GROUP p. 2, 6, 7, 8, 11, 15, 20, 21, 28,
29, 30, 35 charge_limit number
charge_limit_apply character 1 circ_cluster_id
number patron_group_id number
charged_status_display character 1
demerits_applies character 1 max_demerits
number patron_group_code character 10
patron_group_display character 40
patron_group_name character 25 suspension_days
number
19
FISCAL_PERIOD p. 13 The fiscal_period_id field
in FISCAL_PERIOD can be used to link to
fiscal_year_id field in LEDGER. This isnt
obvious from the names. end_date date
fiscal_period_id number fiscal_period_name
character 25 start_date date
20
BIB_MEDIUM The medium field holds the first byte
of the 007 from a bib record. Voyager uses this
table to limit searches by medium in the staff
clients or additional format specification in
Web Voyage. bib_id number medium character
1
21
ELINK_INDEX p. 36 The starred fields in this
table are in UTF-8.   ELINK_INDEX is a very handy
place to find URLs from various types of records.
  Record_type is supposed to be interpreted by
the ELINK_RECORD_TYPE table, but there are some
errors. Actual values for record_type are A for
Authority, B for Bibliographic, E for Electronic
item, and M for MFHD. (ELINK_RECORD_TYPE has a
row, I for Item, but you cant have a URL in an
item.)   The record_id is either an auth_id, a
bib_id, an eitem_id, or a mfhd_id, depending on
the value of record_type.   As a general rule,
the link field is 856u and the link_text field
is subfields z and 3. See Appendix B for more
details.
22
Moral The Data Dictionary is worth its weight in
gold.
23
Will this count the items that circulated during
2007?
24
What does your library do when an item is lost?
Delete the item, delete the MFHD, and delete the
bib?
Change the item status and/or location, opac-suppr
ess the MFHD, and opac-suppress the bib?
Or a little of both?
25
Moral You need to know your librarys
practice.
This is especially important with reserves.
26
Does this query count circulation in 2007 by item
type?
27
A view is a query that lives on the Voyager
server.
You can treat it like a table. But its hard to
know what it does unless you find the SQL on the
server and read it, which isnt easy. In
Voyager, view names end with _VW.
28
CIRCCHARGES_VW
29
648 rows
1190 rows
30
Moral Dont use views, especially in circ and
acq.
These are bad circcharges_vw circrenew_vw item_v
w issue_vw serials_vw
I like these class_vw marc_vw
31
The Views I Use
The marc_vws make fixed fields more
accessible. marcbook_vw marccomputer_vw mar
cmap_vw marcmusic_vw marcserial_vw marcvisu
al_vw The class_vws parse call numbers for
you deweyclass_vw lcclass_vw nalclass_vw
nlmclass_vw sudocclass_vw etc.
32
CALL_NO_TYPE and the CLASS_VWs
In a MFHD, the 852 1st indicator is the class
scheme. 0LC 1Dewey 8Other etc. This
value usually goes into CALL_NO_TYPE in
MFHD_MASTER. CALL_NO_TYPE determines which
CLASS_VW a call number goes into.
33
So you think your library is all LC?
34
With the class_vws you can get nice statistics
by class.
35
Moral Always check call_no_type before using the
class_vws.
36
Why is Grouping turned on in this query?
37
If you need to de-duplicate, use Unique Values.
Right click on the background of the design
pane. Select Properties. Change Unique Values to
Yes.
38
Moral Use Group By like you really mean it.
39
What do these queries do?
40
Better?
41
To enter a description
right-click on the query name and select
Properties.
42
(No Transcript)
43
Another way to annotate
44
To create a Group
right-click anywhere in the Groups pane and
select New Group.
45
Name the New Group
46
To add a query to a Group
Just drag drop
47
To delete a query from a group
highlight it and hit the Delete key. Notice that
youre deleting the shortcut to the query, not
the query itself.
48
Moral Document! Document!
Document!
49
Reuse your queries so you can see trends.
50
Moral Sometimes its more important to be
consistent than to be correct.
51
Thank you!