A Trio of Topics for Confident Access Query Writers

1
A Trio of Topics for Confident Access Query
Writers
CARLI Reports Forum Feb 27, 2009 Cathy
SalikaCARLI
2
Three BIG TopicsOuter JoinsThe BLOB
FunctionsMake Table Queries Subqueries
3
Outer JoinsMost sources call them outer
joins. Access calls them left joins and
right joins. The distinction turns out to be
not very helpful, at least to me.
4
Two reasons to use an outer join1) In case
there are no matching data in a table youre
linking to2) To find records that dont have
matching data in a table youre linking to
5
In case there are no data in a table youre
linking to...What could go wrong with this query?
6
ITEM
ITEM_BARCODE
The normal join on ITEM_ID will give you just one
record.
Remember, you have to have matching ITEM_IDs in
both tables to get a record.
7
So what could go wrong with this query?
Items with no barcode will not appear. MFHDs with
no item will not appear.
8
Think of some other examples where you might link
to a table in which matching data might be
missing.A list of patrons, some of whom might
not have barcodesA list of purchase orders,
some of which might not have invoices yet.A
list of items, some of which might not have
statistical categories. Others?
9
ITEM
How do we fix this? We change the join.
ITEM_BARCODE
10
Howd she do that?Right-click on the link...
... and select Join Properties
11
You get this...
12
Pick option 2, click OK, and the link turns into
an arrow.
So what if I had picked option 3 instead?
13
The arrow would be pointing the other way.
This is the left and right aspect of joining,
but since this...
... is just the same as the picture above, I
dont find left and right very helpful.
14
But it matters which table the arrow is pointing
to!A LOT!!!
15
ITEM
ITEM_BARCODE
16
A list of items, some of which might not have
statistical categories.
17
Moral All the links beyond the arrow have to be
arrows too.
18
A list of patrons, some of whom might not have
barcodes
19
Two reasons to use an outer join1) In case
there are no matching data in a table youre
linking to2) To find records that dont have
matching data in the table youre linking to
20
Suppose I want to find...... the items that
have no barcodes... the bibs that have no
holdings... the patrons who have no
barcodesUse an outer join and check for the
ltNullgt value.The criterion is Is Null
21
Items with no barcodes
22
Bibs with no holdings
23
Patrons with no barcodes
24
Any outer join questions?
25
What can you do now that you couldnt do before
you learned about outer joins?
26
Next topic The BLOB Functions
27
Voyager stores catalog data in two
ways Frequently used data are in their own
fields. Things like TITLE, AUTHOR,
DISPLAY_CALL_NO Fields that need to be
indexed Fields in multi-bib displays The whole
MARC record is stored as a Binary Large OBject.
The BLOB functions let you get at any piece of a
MARC record.
28
The BLOB functions are indispensable, but theyre
slow, so Remember the Alternatives!
29
Alternatives to the BLOB Queries For common
fields, try BIB_TEXT For fields in left-anchored
indexes, try BIB_INDEX For fixed fields, try
MARC_VW (e.g. MARCBOOK_VW) For URLs, try
ELINK_INDEX
30

• BIB_TEXT p. 11, 14, 27, 33, 36
• The starred fields in this table are in UTF-8.
• If you need data from a bib record that are not
  available in BIB_TEXT, check to see if they are
• in BIB_INDEX. Using BIB_INDEX and BIB_TEXT is
  more efficient than using the BLOB functions.
• If youre thinking of using begin_pub_date in a
  criterion, consider using the indexed version of
• this field. Its in the BIB_INDEX table, in the
  normal_heading field when index_code008D.
• ltsnipgt
• Heres how MARC tags map to fields in BIB_TEXT
• Leader byte 5 record_status
• Leader bytes 6-7 bib_format
• Leader byte 17 encoding_level
• ltsnipgt
• 020 a isbn
• 022 a issn
• 024 a other_std_num
• 027 a stdtech
• ltsnipgt
• 100 abcdkq author
• 110 abcdgkn author

31

• Alternatives to the BLOB Queries
• For common fields, try BIB_TEXT
• For fields in left-anchored indexes, try
  BIB_INDEX
• For fixed fields, try MARC_VW (e.g. MARCBOOK_VW)
• For URLs, try ELINK_INDEX
• But when you really need the BLOB, nothing else
  will do

32
There are just 7 BLOB functions to learn
GetAuthBlob GetBibBlob GetMFHDBlob
GetField
GetFieldAll
GetFieldRaw
GetSubField
33
GetAuthBlob(auth_id) GetBibBlob(bib_id) GetMFHDBlo
b(mfhd_id) These three arent useful on their
own. They ask Voyager for a MARC record. You
use one of these as a building block for the
other functions.
34
Your query should include at least one table in
which the ID field is unique, for
example GetBibBlob(BIB_TEXT.BIB_ID) GetBibBl
ob(BIB_MASTER.BIB_ID) GetAuthBlob(AUTH_MASTER
.AUTH_ID) GetMFHDBlob(MFHD_MASTER.MFHD_ID)
BTW, capitalization doesnt matter.
35
GetAuthBlob GetBibBlob GetMFHDBlob
Youll wrap one of these
GetField GetFieldAll GetFieldRaw
around one of these
36
GetField gives you a single occurrence of a MARC
field Syntax
One of the Blob functions
A MARC tag
Which one?
GetField(
,
,
)
Example the first 505 field in a bib
record GetField(GetBibBlob(BIB_TEXT.BIB_ID),5
05,1) Example the first subject (6xx field) in
a bib record GetField(GetBibBlob(BIB_TEXT.BIB_
ID),6,1)
37
Example the first 505 field in a bib
record GetField(GetBibBlob(BIB_TEXT.BIB_ID),5
05,1) v. 1. Ancient Egypt through the Middle
Ages -- v. 2. The Renaissance to the
present. Example the first subject (6xx field)
in a bib record GetField(GetBibBlob(BIB_TEXT.B
IB_ID),6,1) Latin poetry, Medieval and modern
History and criticism
38
GetFieldAll gives you all occurrences of a MARC
field Syntax
One of the Blob functions
A MARC tag
GetFieldAll(
,
)
Example all of the 650 fields in a bib
record GetFieldAll(GetBibBlob(BIB_TEXT.BIB_ID)
,650) Example all of the 866s in a
MFHD GetFieldAll(GetMFHDBlob(MFHD_ID),866)
39
Example all of the 650 fields in a bib
record GetFieldAll(GetBibBlob(BIB_TEXT.BIB_ID)
,650) Job enrichment Employees' representation
in management Personnel management You might
have to make the rows in Access taller to see
them all, because they all appear in one cell.
40
Example all of the 866s in a MFHD GetFieldAll(Ge
tMFHDBlob(MFHD_MASTER.MFHD_ID),866) 0 no.1
(1958)-no. 6 (1962) 0 no. 8 (1964)-no. 11
(1966) 0 no. 16 (1968)-no. 18 (1973-1975)
41
Advanced Features for GetField and
GetFieldAll You may add 2 more parameters to
these functions a list of subfields that you
want to see a separator to appear between
subfields
42
GetField(GetBibBlob(BIB_TEXT.BIB_ID),650) F
orensic psychiatry Illinois Bloomington Case
studies. Example the first 650 field, subfields
a and x GetField(GetBibBlob(BIB_TEXT.BIB_ID),
650,1,ax) Forensic psychiatry Case
studies. Example the first 650 field, subfields
a and x with double dashes between
subfields GetField(GetBibBlob(BIB_TEXT.BIB_ID)
,650,1,axz,--) Forensic psychiatry--Case
studies--Illinois--Bloomington.
43
GetFieldRaw give you one occurrence of a MARC
field, including the tag, indicators, and
subfield coding. Its the only way to get the
indicators. Its the only function that works
with GetSubField. Syntax
One of the Blob functions
A MARC tag
Which one?
)
GetFieldRaw(
,
,
Example the third 650 field in a bib
record GetFieldRaw(GetBibBlob(BIB_TEXT.BIB_ID
),650,3)
44
Example the third 650 field in a bib
record GetFieldRaw(GetBibBlob(BIB_TEXT.BIB_ID
),650,3) 650 0aDay care centersxGovernment
policyzUnited States.
45
GetSubField gives you one occurrence of a MARC
subfield. You need GetFieldRaw and a Blob
function with it. Syntax
A MARC subfield code
Which one?
,
GetSubField(GetFieldRaw(etc),
)
46
Example The second x from the first 650 in a
bib record GetSubField(GetFieldRaw(GetBibBlob(BI
B_TEXT.BIB_ID),650,1),x,2) Bibliography.
47
The Blob functions can be slow, especially for
large databases.Avoid putting a criterion on a
BLOB function.Try to use the BLOB functions on
a subset of your data.
48
To sum up
GetAuthBlob GetBibBlob GetMFHDBlob
GetField
GetFieldAll
GetFieldRaw
GetSubField
49
Questions about the BLOB?
50
What can you do now that you couldnt do before
you learned the BLOB?
51
Make Table Queries and Subqueries Its an Art
52
Make Table Queries and Subqueries fill the same
need in different ways. Well focus on Make
Table Queries first.
53
If you ever say to yourself...I know how to
write this query, except that one of the tables I
need doesnt exist, ...you need a Make Table
Query.
54
Example List all the patron barcodes that appear
more than once in my database.Itd be pretty
easy if you had this table, right?
55
So write a query that builds the table
56
To save the table in your Access database, you
need to make it into a Make Table query
57
Then Access asks what the table should be called.
58
Ill call it Dup Patron Barcode TableTip
You cant have a table and a query with the same
name. If you do, you get an obscure error
message. To keep this from happening, I usually
include table in my table names.When the
query completes, youll get this message
59
If you run the query a second time, Access will
delete the results of the previous run, but it
will ask you first
60
Now, when I look at the tables I have
available...
61
And when I open it
62
List all the patron barcodes that appear more
than once in my database. Thats easy now!
63
Another example I want a list of the items that
are both charged out and damaged.That would be
easy if I had a table listing the charged items
and a table listing the damaged ones.
64
A table of the charged items
65
A table of the damaged items
66
And a query to find the items in both
67
Another use for a Make Table queryIf you have
a Blob query that you know will run for a long
time, make it a Make Table query.Start it
before you leave work for the day.I lock my
workstation and tape a note to the power button
saying that its locked.With luck, in the
morning, it will be ready to paste my results
into the table.
68
(No Transcript)
69
Subquery example patron barcodes that appear
more than once in my database.Write the
subquery. Dont make it a Make Table query.
Dont run it (except to examine and verify the
results). Save it.
70
(No Transcript)
71
When Im about to select the tables for the main
query, click the Queries tab and select Dup
patron barcodes subquery.
72
Then click the Tables tab and select the other
tables that you need.
73
Add the links, save the query, and run it.
74
Questions about Make Table queries and
subqueries?
75

• What can you do now that you know about
• Subqueries and Make Table Queries?

76
Its been a whirlwind tour!
Thank you!