Non-matching records query

  • Thread starter Thread starter Edward
  • Start date Start date
E

Edward

This may be an obvious question, but I'm trying to compare a Part Numbers
field from two tables to find Odd non-matching items. Specifically,
tblProducts vs tblOrderDetails, and list all products in tblOrderDetails not
having a corresponding product in tblProducts, or visa-versa. Then I will
use referential integrity with cascade updates after my updates are
complete.

My ISP news server changed and I lost access to a posting from June 2004
that had this same Q&A.

Thanks
 
Make an Outer Join between the two tables. Right click on join line, choose
Join Properties, select 2 and/or 3 depending on which table you want ALL
records from.
 
Thanks Steve that worked perfect.
Now, one follow-up question involving this and referential integrity if you
would be so kind.

In the tables I mentioned, there are about 230 records in tblProducts that
are not found in tblOrderDetails, as expected. But, I need to setup Ref
Integ so when I update a ProductID(field), the information will cascade
through all records in tblOrderDetails. When I try to invoke the
relationship between ProductID of each table now, with referential
integrity, and Cascade updates, I get the warning message: "Cannot Create
relationship, Data violates rules, data may exist in one table for an item
but not in the other.....". Same response with either join type 1, 2, or 3.
Won't Ref Int work if I do not have Every record from one table used in the
other? Not every record in tblProducts will ever be used in
tblOrderDetails. The way I have been updating records lately is to manually
find each ProductID with a query, then go to each past record and manually
update the record.

Using your answer to my original question, I set the criteria to Is Null for
one side and it gave me a list off all ProductID s not in tblOrderDetails.

Any suggestions appreciated, have a happy new year.
 
Back
Top