Compare tables - help please =)

[cvaccess]

I am trying to compare tables and it is comparing within the table in addition to the NEW and the OLD table. For example, a family is listed in the tables like this:

NEW table
subsc_num last_name
123456789 hubert
123456789 hubert
123456789 smith

OLD table
subsc_num last_name
123456789 hubert
123456789 hubert
123456789 smith

The below query pulls the "smith" record since it looks at the last name within the tables. How can I get this query to pull when they two tables don't match?

SELECT DISTINCTROW NEW.*
FROM NEW INNER JOIN OLD ON NEW.SUBSC_NUM = OLD.SUBSC_NUM
WHERE (((NEW.LAST_NAME)<>[OLD]![LAST_NAME]));

Please help. Thank you.

 

[Golom]

Does not compute. Your query, based on the data that you supplied (i.e. two identical tables with identical values for 'subsc_num' in every record.) should return every record in NEW table. Please re-examine your post and provide a corrected version.

 

[cvaccess]

Both tables, NEW and OLD, have the subsc_num in common. The query displays the differences in last names within the NEW table besides the differences in the two tables. I just want it to show the differences in the two tables and not compare within the NEW table. Hope this is clearer. Thanks.

 

[Golom]

Only slightly clearer. Does &quot;differences&quot; mean &quot;... subsc_num values in the NEW table that do not exist in OLD ... (or vice-versa)&quot; or does it mean &quot;... different Last_Names in the two tables when the subsc_num value is the same ...&quot; ?

For the first case:

Select New.*
From New LEFT JOIN Old ON New.subsc_num = Old.Subsc_num
Where Old.Subsc_num IS NULL

For the second

Select New.*
From New INNER JOIN Old ON New.subsc_num = Old.Subsc_num
Where New.Last_Name <> Old.Last_Name