A trio of topics for confident access query writers
This presentation is the property of its rightful owner.
Sponsored Links
1 / 80

A Trio of Topics for Confident Access Query Writers PowerPoint PPT Presentation


  • 51 Views
  • Uploaded on
  • Presentation posted in: General

A Trio of Topics for Confident Access Query Writers. ELSUG October 8, 2009 Cathy Salika CARLI. Three BIG Topics Outer Joins The BLOB Functions Make Table Queries & Subqueries.

Download Presentation

A Trio of Topics for Confident Access Query Writers

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


A trio of topics for confident access query writers

A Trio of Topics for Confident Access Query Writers

ELSUG

October 8, 2009

Cathy SalikaCARLI


Three big topics outer joins the blob functions make table queries subqueries

Three BIG TopicsOuter JoinsThe BLOB FunctionsMake Table Queries & Subqueries


A trio of topics for confident access query writers

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.


A trio of topics for confident access query writers

Two reasons to use an outer join:1) In case there are no matching data in a table you’re linking to2) To find records that don’t have matching data in a table you’re linking to


In case there are no data in a table you re linking to what could go wrong with this query

In case there are no data in a table you’re linking to...What could go wrong with this query?


A trio of topics for confident access query writers

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.


So what could go wrong with this query

So what could go wrong with this query?

Items with no barcode will not appear.

MFHDs with no item will not appear.


A trio of topics for confident access query writers

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?


A trio of topics for confident access query writers

ITEM

How do we fix this?

We change the join.

ITEM_BARCODE


How d she do that right click on the link

How’d she do that?Right-click on the link...

... and select Join Properties


You get this

You get this...


Pick option 2 click ok and the link turns into an arrow

Pick option 2, click OK, and the link turns into an arrow.

So what if I had picked option 3 instead?


The arrow would be pointing the other way

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 don’t find “left” and “right” very helpful.


But it matters which table the arrow is pointing to a lot

But it matters which table the arrow is pointing to!A LOT!!!


A trio of topics for confident access query writers

ITEM

ITEM_BARCODE


A trio of topics for confident access query writers

A list of items, some of which might not have statistical categories.


Moral all the links beyond the arrow have to be arrows too

Moral: All the links beyond the arrow have to be arrows too.


A trio of topics for confident access query writers

A list of patrons, some of whom might not have

barcodes


A trio of topics for confident access query writers

Two reasons to use an outer join:1) In case there are no matching data in a table you’re linking to2) To find records that don’t have matching data in the table you’re linking to


A trio of topics for confident access query writers

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 <Null> value.The criterion is: Is Null


A trio of topics for confident access query writers

Items with no barcodes


A trio of topics for confident access query writers

Bibs with no holdings


A trio of topics for confident access query writers

Patrons with no barcodes


A trio of topics for confident access query writers

Any outer join questions?


A trio of topics for confident access query writers

What can you do now that you couldn’t do before you learned about outer joins?


A trio of topics for confident access query writers

Next topic: The BLOB Functions


A trio of topics for confident access query writers

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.


A trio of topics for confident access query writers

The BLOB functions are indispensable, but they’re slow, so

Remember the Alternatives!


A trio of topics for confident access query writers

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


A trio of topics for confident access query writers

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 you’re thinking of using begin_pub_date in a criterion, consider using the indexed version of

this field. It’s in the BIB_INDEX table, in the normal_heading field when index_code=008D.

<snip>

Here’s how MARC tags map to fields in BIB_TEXT:

Leader byte 5record_status

Leader bytes 6-7bib_format

Leader byte 17encoding_level

<snip>

020 aisbn

022 aissn

024 aother_std_num

027 astdtech

<snip>

100 abcdkqauthor

110 abcdgknauthor

111 acdegknauthor

245 abcfghknpstitle

245 abtitle_brief

130 adfgklmnoprsuniform_title


A trio of topics for confident access query writers

There are just 7 BLOB functions to learn

GetAuthBlob

GetBibBlob

GetMFHDBlob

GetField

GetFieldAll

GetFieldRaw

GetSubField


A trio of topics for confident access query writers

GetAuthBlob(auth_id)

GetBibBlob(bib_id)

GetMFHDBlob(mfhd_id)

These three aren’t useful on their own. They ask Voyager for a MARC record. You use one of these as a building block for the other functions.


A trio of topics for confident access query writers

Your query should include at least one table in which the ID field is unique, for example:

GetBibBlob([BIB_TEXT].[BIB_ID])

GetBibBlob([BIB_MASTER].[BIB_ID])

GetAuthBlob([AUTH_MASTER].[AUTH_ID])

GetMFHDBlob([MFHD_MASTER].[MFHD_ID])

BTW, capitalization doesn’t matter.


A trio of topics for confident access query writers

GetAuthBlob

GetBibBlob

GetMFHDBlob

You’ll wrap one of these

GetField

GetFieldAll

GetFieldRaw

around one of these


A trio of topics for confident access query writers

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]),’505’,1)

Example: the first subject (6xx field) in a bib record:

GetField(GetBibBlob([BIB_TEXT].[BIB_ID]),’6’,1)


A trio of topics for confident access query writers

Example: the first 505 field in a bib record

GetField(GetBibBlob([BIB_TEXT].[BIB_ID]),’505’,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].[BIB_ID]),’6’,1)

Latin poetry, Medieval and modern History and criticism


A trio of topics for confident access query writers

A Blob Function in a Query


A trio of topics for confident access query writers

Using Shift-F2 to Zoom


A trio of topics for confident access query writers

Zoom works with criteria too.

You can resize the font to improve readability.


A trio of topics for confident access query writers

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’)


A trio of topics for confident access query writers

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.


A trio of topics for confident access query writers

Example: all of the 866s in a MFHD:

GetFieldAll(GetMFHDBlob([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)


A trio of topics for confident access query writers

“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


A trio of topics for confident access query writers

GetField(GetBibBlob([BIB_TEXT].[BIB_ID]),”650”)

Forensic 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, x and z with double dashes between subfields

GetField(GetBibBlob([BIB_TEXT].[BIB_ID]),”650”,1,”axz”,”--”)

Forensic psychiatry--Case studies--Illinois--Bloomington.


A trio of topics for confident access query writers

GetFieldRaw give you one occurrence of a MARC field, including the tag, indicators, and subfield coding.

* It’s the only way to get the indicators.

* It’s 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)


A trio of topics for confident access query writers

Example: the third 650 field in a bib record:

GetFieldRaw(GetBibBlob([BIB_TEXT].[BIB_ID]),’650’,3)

650 0aDay care centersxGovernment policyzUnited States.


A trio of topics for confident access query writers

Use the Mid function to isolate the indicators.

245: getfieldraw(getbibblob([bib_text].[bib_id]),'245',1)

Ind1: Mid(getfieldraw(getbibblob([bib_text].[bib_id]),'245',1),4,1)

Ind2: Mid(getfieldraw(getbibblob([bib_text].[bib_id]),'245',1),5,1)


A trio of topics for confident access query writers

Ind1: Mid(getfieldraw(getbibblob([bib_text].[bib_id]),'245',1),4,1)

Ind2: Mid(getfieldraw(getbibblob([bib_text].[bib_id]),'245',1),5,1)

245: getfieldraw(getbibblob([bib_text].[bib_id]),'245',1)


A trio of topics for confident access query writers

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~),

)


A trio of topics for confident access query writers

Example: The second $x from the first 650 in a bib record

GetSubField(GetFieldRaw(GetBibBlob([BIB_TEXT].[BIB_ID]),’650’,1),’x’,2)

Bibliography.


A trio of topics for confident access query writers

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.


A trio of topics for confident access query writers

To sum up:

GetAuthBlob

GetBibBlob

GetMFHDBlob

GetField

GetFieldAll

GetFieldRaw

GetSubField


Questions about the blob

Questions about the BLOB?


What can you do now that you couldn t do before you learned the blob

What can you do now that you couldn’t do before you learned the BLOB?


Make table queries and subqueries it s an art

Make Table Queries and Subqueries: It’s an Art


A trio of topics for confident access query writers

Make Table Queries and Subqueries fill the same need in different ways. We’ll focus on Make Table Queries first.


A trio of topics for confident access query writers

If you ever say to yourself...“I know how to write this query, except that one of the tables I need doesn’t exist,” ...you need a Make Table Query.


A trio of topics for confident access query writers

Example: List all the patron barcodes that appear more than once in my database and show who they’re assigned to.It’d be pretty easy if you had this table, right?


So write a query that builds the table

So write a query that builds the table:


To save the table in your access database you need to make it into a make table query

To save the table in your Access database, you need to make it into a Make Table query:


Then access asks what the table should be called

Then Access asks what the table should be called.


A trio of topics for confident access query writers

I’ll call it “Dup Patron Barcode Table”Tip: You can’t 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, you’ll get this message:


A trio of topics for confident access query writers

If you run the query a second time, Access will delete the results of the previous run, but it will ask you first:


Now when i look at the tables i have available

Now, when I look at the tables I have available...


And when i open it

And when I open it…


List all the patron barcodes that appear more than once in my database that s easy now

List all the patron barcodes that appear more than once in my database. That’s easy now!


A trio of topics for confident access query writers

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.


A table of the charged items

A table of the charged items:


A table of the damaged items

A table of the damaged items:


And a query to find the items in both

And a query to find the items in both:


A trio of topics for confident access query writers

Another use for a Make Table query:If 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 it’s locked.With luck, in the morning, it will be ready to paste my results into the table.


A trio of topics for confident access query writers

Subquery example: patron barcodes that appear more than once in my database.Write the subquery. Don’t make it a Make Table query. Don’t run it (except to examine and verify the results). Save it.


A trio of topics for confident access query writers

When I’m about to select the tables for the main query, click the Queries tab and select “Dup patron barcodes subquery”.


Then click the tables tab and select the other tables that you need

Then click the Tables tab and select the other tables that you need.


Add the links save the query and run it

Add the links, save the query, and run it.


Questions about make table queries and subqueries

Questions about Make Table queries and subqueries?


A trio of topics for confident access query writers

What can you do now that you know about

Subqueries and Make Table Queries?


It s been a whirlwind tour

It’s been a whirlwind tour!

Thank you!


  • Login