Comparin unmatched records in two tables

[Payal Patel]

Hi,
I have two tables that have the same fields. I want to compare the two tables by each field. So if one table does not have the same exact records as the other, then i want it to spit back what rows the two tables do not have in common. I tried using the unmatched query wizard, but it only lets me compare one field. Does someone have a suggestion?
Thanks a lot
P.

 

[Guus2005]

can be accomplished by using two queries. Assuming there is an ID field present.
first a query where you join each field of both tables something like:

select t1.* from t1 inner join t2 on t1.field1 = t2.field1 and t1.field2 = t2.field2

This query retrieves all matching records in both tables. Store it as qryMatch and use it in the next query

select t1.* from t1 where t1.ID not in (select ID from qryMatch)

The second query returns all records from t1 where there is not a match between t1 and t2.
To view the result in one query:

[code]select 1 as tablename, t1.* from t1 where t1.ID not in (select ID from qryMatch) 
union
select 2 as tablename, t2.* from t2 where t2.ID not in (select ID from qryMatch)

The first column shows where the result comes from, the first or second table.

HTH

 

[namliam]

can be accomplished by using two ONE queries.

Just use an unmatched query (default wizard option) this uses a outer join which does this in one query.