Unmatched query where non match is on 2 fields

K

kayabob

I have an illustration with a very small amount of sample data to help
explain my question at this web address:
http://www.tcswebs.com/zdatabase.htm. I need to find the instances where the
combination of the two fields in one table Do NOT have matches to the same
combination of fields in a related table. In my example, both Safe ID AND
CustomerID in tblDocs must have a match to SafeID AND CustomerID in
tblContCusts. So, Safe 1 / 1111 in tblDocs is valid because it has a match
in tblContCusts to Safe 1 / 1111. A data validity problem occurs ( and this
is what I ultimately need to track down) when Safe 2 / 3333 in tblDocs DOES
NOT have a match on both fields in tblContCusts. It is NOT good enough that
Safe 2 matches on a single field in tblContCusts. The lower table on my
illustration is what I would like to get from a query - it shows me an
invalid record (i.e., no match from tblDocs to tlbCusts) that I need to track
down and fix. I have beat my brains out and cant figure out how to find the
Unmatched records for both corresponding fields. I've also tried playing
with join types but that was not any help. Any help is much appreciated.
 
A

ambradnum

Try something like this:

SELECT SafeID, CustomerID, DocID
FROM tblDocs
WHERE SafeID & '~' & CustomerID NOT IN
(SELECT SafeID & '~' & CustomerID
FROM tblContCust)

I'm not sure whether it's & or +

Regards
Alan
 
J

John Vinson

I have an illustration with a very small amount of sample data to help
explain my question at this web address:
http://www.tcswebs.com/zdatabase.htm. I need to find the instances where the
combination of the two fields in one table Do NOT have matches to the same
combination of fields in a related table. In my example, both Safe ID AND
CustomerID in tblDocs must have a match to SafeID AND CustomerID in
tblContCusts. So, Safe 1 / 1111 in tblDocs is valid because it has a match
in tblContCusts to Safe 1 / 1111. A data validity problem occurs ( and this
is what I ultimately need to track down) when Safe 2 / 3333 in tblDocs DOES
NOT have a match on both fields in tblContCusts. It is NOT good enough that
Safe 2 matches on a single field in tblContCusts. The lower table on my
illustration is what I would like to get from a query - it shows me an
invalid record (i.e., no match from tblDocs to tlbCusts) that I need to track
down and fix. I have beat my brains out and cant figure out how to find the
Unmatched records for both corresponding fields. I've also tried playing
with join types but that was not any help. Any help is much appreciated.
Click to expand...

A "Frustrated Outer Join" joining on two fields should work:

SELECT tblDocs.*
FROM tblDocs LEFT JOIN tblContCusts
ON tblDocs.SafeID = tblContCusts.SafeID
AND tblDOcs.CustomerID = tblContCusts.SafeID
WHERE tblContCusts.SafeID IS NULL;

John W. Vinson[MVP]
 
K

kayabob

Thanks, I will give both of these a try today. On a simpler level, in the
night it occured to me that for the query I can concatenate the two fields in
each database for the query, run an unmatched query.....and on first look
that may also work. Thanks for the help.
 
K

kayabob

Alan, this works perfectly, thank you. I have a question on the place where
it concatenates. Is the '~' just a delimeter for the concatenation, or is
the ~ a significant character of some sort?
Thanks
 
A

ambradnum

It's just a delimiter.
Alan, this works perfectly, thank you. I have a question on the place where
it concatenates. Is the '~' just a delimeter for the concatenation, or is
the ~ a significant character of some sort?
Thanks
Click to expand...
 
Top