login about faq

As of February 2015 Libcatcode is no longer accepting new questions. This site will stay up as the owner decides the next steps for preserving the content on the site. Thank you all for your support in the past three years!

Hello!

I'm new to SQL but need to run a report through access.

Ideally I'm hoping to get a report of Titles BY Locations with Bib # & Call # -- is this possible?

asked Oct 23 '14 at 14:55

gokmlibrarian's gravatar image

gokmlibrarian
112


I'm not sure if you're hoping to sort the titles by Location Code (what's displayed in 852 subfield b) or by it's name (what you'd see in the item record).

Anyway, if I understand you correctly, here's the SQL if you want the code

SELECT LOCATION.LOCATION_CODE, BIB_TEXT.TITLE, BIB_TEXT.BIB_ID, MFHD_MASTER.DISPLAY_CALL_NO
FROM ((BIB_TEXT INNER JOIN BIB_MFHD ON BIB_TEXT.BIB_ID = BIB_MFHD.BIB_ID) INNER JOIN MFHD_MASTER ON BIB_MFHD.MFHD_ID = MFHD_MASTER.MFHD_ID) INNER JOIN LOCATION ON MFHD_MASTER.LOCATION_ID = LOCATION.LOCATION_ID
ORDER BY LOCATION.LOCATION_CODE;

or, here it is if you'd like the name

SELECT LOCATION.LOCATION_NAME, BIB_TEXT.TITLE, BIB_TEXT.BIB_ID, MFHD_MASTER.DISPLAY_CALL_NO
FROM ((BIB_TEXT INNER JOIN BIB_MFHD ON BIB_TEXT.BIB_ID = BIB_MFHD.BIB_ID) INNER JOIN MFHD_MASTER ON BIB_MFHD.MFHD_ID = MFHD_MASTER.MFHD_ID) INNER JOIN LOCATION ON MFHD_MASTER.LOCATION_ID = LOCATION.LOCATION_ID
ORDER BY LOCATION.LOCATION_NAME;

Hope, that helps.

link

answered Oct 23 '14 at 15:40

Lokimotive's gravatar image

Lokimotive
211

I read your question slightly differently, thinking that you'd like you specify a location and get a report of those other things. If that's the case, you might try something like:

SELECT BIB_TEXT.BIB_ID, BIB_TEXT.TITLE, MFHD_MASTER.DISPLAY_CALL_NO, MFHD_MASTER.NORMALIZED_CALL_NO, LOCATION.LOCATION_CODE
FROM (BIB_MASTER INNER JOIN (BIB_MFHD INNER JOIN (MFHD_MASTER INNER JOIN LOCATION ON MFHD_MASTER.LOCATION_ID = LOCATION.LOCATION_ID) ON BIB_MFHD.MFHD_ID = MFHD_MASTER.MFHD_ID) ON BIB_MASTER.BIB_ID = BIB_MFHD.BIB_ID) INNER JOIN BIB_TEXT ON BIB_MASTER.BIB_ID = BIB_TEXT.BIB_ID
WHERE ((BIB_MASTER.SUPPRESS_IN_OPAC="N") AND (MFHD_MASTER.SUPPRESS_IN_OPAC="N") AND (LOCATION.LOCATION_CODE="zemkat"));

I used "zemkat" as the sample location code near the end, so you'd have to substitute in the location you want (the form you put in mfhd 852 $b). This query will also ignore bibs/mfhds that are suppressed.

If you use LC call numbers, you might find the NORMALIZED_CALL_NO column useful, as it will let you sort the titles in shelving order (which DISPLAY_CALL_NO will not do correctly.)

link

answered Oct 23 '14 at 15:57

zemkat's gravatar image

zemkat
152144

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or __italic__
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×2
×1
×1

Asked: Oct 23 '14 at 14:55

Seen: 2,170 times

Last updated: Oct 23 '14 at 15:57

Powered by hamsters in the server | CSS skin by prtk