Finding Unmatched Values in One Category When There are Matches in Another

Access 1.
Image via Wikipedia

This tip was tested in Microsoft Access 2007 but should work in all versions of Access and with slight variations in any flavor of SQL.

If you have hung around Access long enough, you have probably done a “Find Unmatched” query, and if you looked at the SQL view it probably looked something like this:

INSERT INTO ProductLists ( Description, LinkField, LinkText )

SELECT Static.ATTRIBUTE, "ATTRIBUTE", Static.ATTRIBUTE

FROM Static LEFT JOIN ProductLists ON Static.ATTRIBUTE = ProductLists.LinkText

WHERE (((ProductLists.LinkText) Is Null))

GROUP BY Static.ATTRIBUTE, "ATTRIBUTE", Static.ATTRIBUTE;

Admittedly, a lot of this can be ignored. The key here is the JOIN which lets us find a match for everything we can and then the WHERE clause which selects only the non-matches.

But that all falls apart if I have matches that should not count for some reason. For example, I frequently find a match in one category and I need to know for sure that I do not have a match in different category.

If you simply test for no match like I did above, you miss everything that has a match in the wrong category. If you test for Null or a match in a different category, you get false positives if it is in both categories.

I have, in the past set up a seperate query to filter down to my test value and then done an “unmatched” against that. Doing that frequently, though, leaves you with a lot of extra queries to keep track of, and I really never liked that option. So instead, I just added a second requirement to the ON statement in the FROM clause, like this:

INSERT INTO ProductLists ( Description, LinkField, LinkText )

SELECT Static.ATTRIBUTE, "ATTRIBUTE", Static.ATTRIBUTE

FROM Static LEFT JOIN ProductLists ON (Static.ATTRIBUTE = ProductLists.LinkText

AND (ProductLists.LinkText) = "ATTRIBUTE")

WHERE (((ProductLists.LinkText) Is Null))

GROUP BY Static.ATTRIBUTE, "ATTRIBUTE", Static.ATTRIBUTE;

The only catch is that after you do this you will not be able to view it in Design mode, but the results are, in my opinion, worth it.

Reblog this post [with Zemanta]
  • Share/Bookmark

Related posts:

  1. “How to Write a Web Site” Group Project – Badge code
  2. All I want for Christmas is a Amazon Kindle
  3. Death of the Salesman: A new way to sell cars
  4. World Leader in Technology and World Leader in Economic Growth Together at Last!
  5. Five Blogs in One: Which one will your read?
This entry was posted in For Geeks. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>