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!

I am still very new to Access/SQL but I feel like this query should work, but it's not.

The goal of my query -- find serial records where the item location and the MFHD location don't match. It runs, but it comes up blank. I know this situation exists, so I'm not sure why it's not finding anything.

SELECT BIB_TEXT.BIB_ID, BIB_TEXT.BIB_FORMAT, BIB_TEXT.TITLE, MFHD_MASTER.MFHD_ID, MFHD_MASTER.LOCATION_ID, LOCATION.LOCATION_CODE, MFHD_ITEM.ITEM_ID, ITEM.PERM_LOCATION, [LOCATION-].LOCATION_CODE, ([MFHD_MASTER]![LOCATION_ID]<>[ITEM]![PERM_LOCATION]) AS DiffLocCodes FROM [LOCATION-] INNER JOIN ((BIB_TEXT INNER JOIN BIB_MFHD ON BIB_TEXT.BIB_ID = BIB_MFHD.BIB_ID) INNER JOIN (((MFHD_MASTER INNER JOIN MFHD_ITEM ON MFHD_MASTER.MFHD_ID = MFHD_ITEM.MFHD_ID) INNER JOIN LOCATION ON MFHD_MASTER.LOCATION_ID = LOCATION.LOCATION_ID) INNER JOIN ITEM ON MFHD_ITEM.ITEM_ID = ITEM.ITEM_ID) ON BIB_MFHD.MFHD_ID = MFHD_MASTER.MFHD_ID) ON [LOCATION-].LOCATION_ID = ITEM.ITEM_ID WHERE (((BIB_TEXT.BIB_FORMAT) Like "as") AND (Not (([MFHD_MASTER]![LOCATION_ID]<>[ITEM]![PERM_LOCATION]))=0)) ORDER BY BIB_TEXT.TITLE;

Any help is much appreciated! -Jen

asked Feb 14 '12 at 13:53

jen_young's gravatar image

jen_young
21118

edited Feb 14 '12 at 13:58


Try this:

SELECT DISTINCT BIB_TEXT.BIB_ID, MFHD_MASTER.MFHD_ID, BIB_TEXT.TITLE, LOCATION.LOCATION_CODE AS MFHD_LOC, LOCATION_1.LOCATION_CODE AS ITEM_LOC
FROM LOCATION AS LOCATION_1 INNER JOIN ((BIB_TEXT INNER JOIN (BIB_MFHD INNER JOIN ((MFHD_ITEM INNER JOIN MFHD_MASTER ON MFHD_ITEM.MFHD_ID = MFHD_MASTER.MFHD_ID) INNER JOIN ITEM ON MFHD_ITEM.ITEM_ID = ITEM.ITEM_ID) ON BIB_MFHD.MFHD_ID = MFHD_MASTER.MFHD_ID) ON BIB_TEXT.BIB_ID = BIB_MFHD.BIB_ID) INNER JOIN LOCATION ON MFHD_MASTER.LOCATION_ID = LOCATION.LOCATION_ID) ON LOCATION_1.LOCATION_ID = ITEM.PERM_LOCATION
WHERE (((BIB_TEXT.BIB_FORMAT)="as") AND ((MFHD_MASTER.LOCATION_ID)<>[ITEM].[PERM_LOCATION]))
ORDER BY BIB_TEXT.TITLE;
link

answered Feb 14 '12 at 15:39

zemkat's gravatar image

zemkat
152144

edited Feb 14 '12 at 15:49

Thank you SO much!! I tweaked it a bit (added call nos, owning libraries) but it worked! Now ~1400 records to correct.

Do you or anyone else have any sources you'd recommend for learning SQL?

-Jen

link

answered Feb 14 '12 at 16:17

jen_young's gravatar image

jen_young
21118

1

Here are a few learning options:

Learn to Code the Hard Way has a SQL component: http://sql.learncodethehardway.org/

w3schools has a SQL course: http://www.w3schools.com/sql/default.asp

Java2s has example SQL code: http://www.java2s.com/Code/SQL/CatalogSQL.htm

Lynda.com (license required) has a SQL Essentials course: http://www.lynda.com/SQL-tutorials/essential-training/769-2.html

(Feb 14 '12 at 16:31) slmcdanold
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: Feb 14 '12 at 13:53

Seen: 5,426 times

Last updated: Feb 14 '12 at 16:31

Related questions

Powered by hamsters in the server | CSS skin by prtk