Advanced Report Writing

1
Advanced Report Writing
IUAG Reports Forum May 11, 2006 Cathy
SalikaCARLI Staff
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 the 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
Next topic The BLOB Functions
26
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.
27
The BLOB functions are indispensable, but theyre
slow, so Remember the Alternatives!
28
Alternatives to the BLOB Queries is on the web
site. BIB_TEXT BIB_INDEX For fixed fields,
MARC_VW (e.g. MARCBOOK_VW) For URLs,
ELINK_INDEX But when you need the BLOB...
29
There are just 7 BLOB functions to learn
GetAuthBlob GetBibBlob GetMFHDBlob
GetField
GetFieldAll
GetFieldRaw
GetSubField
30
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 the building block for the
other functions.
31
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.
32
GetAuthBlob GetBibBlob GetMFHDBlob
Youll wrap one of these
GetField GetFieldAll GetFieldRaw
around one of these
33
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)
34
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
35
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_MASTER.BIB_I
D),866)
36
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 the expand the rows in Access to see them
all, because they all appear in one cell.
37
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)
38
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
39
Example the first 650 field, subfields a, x, and
z GetField(GetBibBlob(BIB_TEXT.BIB_ID),650,1
,axz) Forensic psychiatry Periodicals. Example
the first 650 field, subfields a, x, and z with
double dashes between subfields GetField(GetBibBlo
b(BIB_TEXT.BIB_ID),650,1,axz,-
-) Forensic psychiatry--Periodicals.
40
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)
41
Example the third 650 field in a bib
record GetFieldRaw(GetBibBlob(BIB_TEXT.BIB_ID
),650,3) 650 0aDay care centersxGovernment
policyzUnited States.
42
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),
)
43
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.
44
The Blob functions can be slow, especially for
large databases.Sounds familiar? Check this
outA Strategy for Using BLOB Functions on
Large Databases
45
To sum up
GetAuthBlob GetBibBlob GetMFHDBlob
GetField
GetFieldAll
GetFieldRaw
GetSubField
46
Questions about the BLOB?
47
Make Table Queries and Subqueries Its an Art
48
Make Table Queries and subqueries fill the same
need in different ways. Well focus on Make
Table Queries first.
49
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.
50
Example List all the patrons who have duplicate
barcodes.Itd be pretty easy if you had this
table, right?
51
So write a query that builds the table
52
To save the table in your Access database, you
need to make it into a Make Table query
53
Then Access asks what the table should be called.
54
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
55
If you run the query a second time, Access will
delete the results of the previous run, but it
will ask you first
56
Now, when I look at the tables I have
available...
57
List all the patrons who have duplicate barcodes.
Thats easy now!
58
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.
59
A table of the charged items
60
A table of the damaged items
61
And a query to find the items in both
62
(No Transcript)
63
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.
64
Subquery example patrons with duplicate barcodes
again.Write the subquery. Dont make it a
Make Table query. Dont run it (except to
examine and verify the results). Save it.
65
(No Transcript)
66
When Im about to select the tables for the main
query, click the Queries tab and select Dup
patron barcodes subquery.
67
Then click the Tables tab and select the other
tables that you need.
68
Add the links, save the query, and run it.
69
Questions about Make Table queries and
subqueries?
70
Its been a whirlwind tour!
Thank you!
71
Documentation on functions in AccessTheyre
generally the same as Visual Basic Functions. I
use this web sitehttp//msdn.microsoft.com/libr
ary/default.asp?url/library/en-us/
vblr7/html/vaoriFunctionsVBA.asp