Comparing two tables to find non-similar records

[kbsudhir]

Hi All,



I want to create a query which compares common field of two tables & should provide the list of records which are not common from Table 1.

SELECT [Table1].Material
FROM Table1, Table2 WHERE ((([Table1].Material)<>[Table2].[Material]));

Both table have more than 200000 records.

But when I run the query I get more than 500000 records ... I don't know from where.
I just want to get the records from Table1 which are not common in Table2.

Please guide to get this done.

Regards
Sudhir

[recyan]

Have you tried using the
Find Unmatched Query Wizard
in the
Query Wizard ?

Thanks

[kbsudhir]

Thanks recyan. I really forgot about that. But got it done by writing the below query.

SELECT [Table1].[material]
FROM Table1 WHERE material NOT IN
(SELECT [Table2].[material] FROM [Table2]);

Thanks
Sudhir

[recyan]

Glad you got things working.
I don't know which of the 2 is more efficient when used with lots of data.
Let's hope some one comes along & comments on that.

Thanks