Transcript Document 7815363

What’s the Worst that could
Happen?
Trouble-Shooting Your Voyager Queries
ELSUG
October 8, 2009
Cathy Salika
CARLI
1
Will this query tell me how many
items I have in each location?
2
Moral: Always
remove extra
tables from your
queries.
3
Will this query tell me how many
MFHDs I have in each location?
4
5
Null is a special value
This library has 14,470 MFHDs
0 MFHDs
13,071 MFHDs
6
The missing MFHDs have a
value of Null
1,399 MFHDs
7
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!
8
Moral: Never
Count a field
that might have a
null value in it.
The _ID fields usually do not have a null value.
9
Null is a special value
This library has 14,470 MFHDs
0 MFHDs
13,071 MFHDs
10
11
Moral: Use simple
queries to check
the results of
complicated ones.
12
Will this count the items that
circulated during 2007?
13
Moral: Dates
default to 12:00
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#
14
Moral: You need
to know what’s in
the tables.
When an item is discharged, the circ
transaction moves from CIRC_TRANSACTIONS
to CIRC_TRANS_ARCHIVE.
15
How am I supposed to know
that for every table???
The Data Dictionary!
CARLI’s version of the Data Dictionary is available
on EL Commons.
16
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 you’ll find it.
The charge_type and discharge_type fields have 2 values, N for Normal
and O for Override.
17
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
18
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
isn’t obvious from the names.
end_date date
fiscal_period_id number
fiscal_period_name character 25
start_date date
19
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
20
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 can’t 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 856$u and the link_text field is
subfields $z and $3. See Appendix B for more details.
21
Moral: The Data
Dictionary is
worth its weight
in gold.
22
Will this count the items that
circulated during 2007?
23
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-suppress the MFHD, and
opac-suppress the bib?
Or a little of both?
24
Moral: You need to
know your
library’s practice.
This is especially important with reserves.
25
Does this query count
circulation in 2007 by item type?
26
A view is a query that lives on
the Voyager server.
You can treat it like a table.
But it’s hard to know what it does unless
you find the SQL on the server and read it,
which isn’t easy.
In Voyager, view names end with _VW.
27
CIRCCHARGES_VW
28
648 rows
1190 rows
29
Moral: Don’t use
views, especially
in circ and acq.
These are bad:
circcharges_vw
circrenew_vw
item_vw
issue_vw
serials_vw
I like these:
*class_vw
marc*_vw
30
The Views I Use
The marc*_vw’s make fixed fields more accessible.
marcbook_vw
marcmusic_vw
marccomputer_vw
marcserial_vw
marcmap_vw
marcvisual_vw
The *class_vw’s parse call numbers for you:
deweyclass_vw
nlmclass_vw
lcclass_vw
sudocclass_vw
nalclass_vw
etc.
31
CALL_NO_TYPE and the
*CLASS_VW’s
In a MFHD, the 852 1st indicator is the class scheme.
0=LC 1=Dewey 8=Other etc.
This value usually goes into CALL_NO_TYPE in
MFHD_MASTER.
CALL_NO_TYPE determines which *CLASS_VW a call
number goes into.
32
So you think your library is all
LC?
33
With the *class_vw’s you can
get nice statistics by class.
34
Moral: Always
check call_no_type
before using the
*class_vw’s.
35
Why is Grouping turned on in
this query?
36
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.
37
Moral: Use Group
By like you really
mean it.
38
What do these queries do?
39
Better?
40
To enter a description…
right-click on the
query name and
select Properties.
41
42
Another way to annotate
43
To create a Group…
right-click
anywhere in the
Groups pane and
select New Group.
44
Name the New Group
45
To add a query to a Group
Just drag & drop
46
To delete a query from a group
…highlight it and hit the Delete key.
Notice that you’re deleting the shortcut to the query,
not the query itself.
47
Moral:
Document!
Document!
Document!
48
Reuse your queries so you can
see trends.
49
Moral: Sometimes
it’s more important
to be consistent
than to be correct.
50
Thank you!
51