- 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;
![Reblog this post [with Zemanta]](http://img.zemanta.com/reblog_e.png?x-id=3c094638-9849-4eee-b7f6-12e6882833cb)
Related posts:
