Find unmatched records

[nissan240zx]

Hell All,
Before I begin let me say I dont have Unmatched Query wizard installed on my computer.

Here is what i am trying to find.
I got a table with 5 fields (Account #, Customer #, Region Code, Zip Code etc). This table contains both old and new informaiton. I means old zip code and new ones.
I have split the table with 2 queries. One that gives me information on old records and other that gives me information on new records. The problem is that list for new records also contains some old ones as that informaiton hasnt changed.

Now what I want to do is..compare the 2 queries and get the ones that are not matching.
I tried to use the "Not In" method but didnt work. I am sure I did wrong.

So for example sake you can use Query1 (master query) and Query2 (Child query) and I want to know which records are not matching in child in comparison to master.

Please advice..

Thanks in advance..


A good programmer is someone who looks both ways before crossing a one-way street. - Doug Linder

 

[Remou]

Hi
Zip Codes that exist in the master query, but not in the child query:
[tt]SELECT MasterQry.ZipCode
FROM MasterQry LEFT JOIN ChildQry ON MasterQry.ZipCode = ChildQry.ZipCode
WHERE (((ChildQry.ZipCode) Is Null));[/tt]

Accounts that have different zip codes:
[tt]SELECT MasterQry.ZipCode, ChildQry.ZipCode
FROM MasterQry INNER JOIN ChildQry ON MasterQry.AccountNo = ChildQry.AccountNo
WHERE (((ChildQry.ZipCode)<>[masterqry].[zipcode]));[/tt]

Zip Codes that exist in the master query, but not in the child query:
[tt]SELECT MasterQry.ZipCode, ChildQry.ZipCode
FROM ChildQry LEFT JOIN MasterQry ON ChildQry.ZipCode = MasterQry.ZipCode
WHERE (((MasterQry.ZipCode) Is Null));[/tt]

 

[nissan240zx]

I want to compare based on the whole record and not just zipcode.


A good programmer is someone who looks both ways before crossing a one-way street. - Doug Linder

 

[Golom]

Then you're pretty much stuck with placing all the fields in the ON condition. It'll probably run like a pig though since (I assume) you don't have indexes built for every field in both tables.

There are other ways to do this using VB and ADO but comparison of complete records is always a time-consuming process.