1 / 80

A Trio of Topics for Confident Access Query Writers

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.

lore
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. A Trio of Topics for Confident Access Query Writers ELSUG October 8, 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 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

  5. In case there are no data in a table you’re 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. How’d 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 don’t 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 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

  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 <Null> 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 couldn’t 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 they’re 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 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 5 record_status Leader bytes 6-7 bib_format Leader byte 17 encoding_level <snip> 020 a isbn 022 a issn 024 a other_std_num 027 a stdtech <snip> 100 abcdkq author 110 abcdgkn author 111 acdegkn author 245 abcfghknps title 245 ab title_brief 130 adfgklmnoprs uniform_title

  31. There are just 7 BLOB functions to learn GetAuthBlob GetBibBlob GetMFHDBlob GetField GetFieldAll GetFieldRaw GetSubField

  32. 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.

  33. 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.

  34. GetAuthBlob GetBibBlob GetMFHDBlob You’ll wrap one of these GetField GetFieldAll GetFieldRaw around one of these

  35. 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)

  36. 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

  37. A Blob Function in a Query

  38. Using Shift-F2 to Zoom

  39. Zoom works with criteria too. You can resize the font to improve readability.

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

  41. 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.

  42. 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)

  43. “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

  44. 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.

  45. 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)

  46. Example: the third 650 field in a bib record: GetFieldRaw(GetBibBlob([BIB_TEXT].[BIB_ID]),’650’,3) 650 0aDay care centersxGovernment policyzUnited States.

  47. 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)

  48. 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)

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

  50. Example: The second $x from the first 650 in a bib record GetSubField(GetFieldRaw(GetBibBlob([BIB_TEXT].[BIB_ID]),’650’,1),’x’,2) Bibliography.

More Related