Compare values in separate tables

[ehale]

Hello,

I could use some help on a problem I'm having . . . I have 2 tables in a
database that have the same fields (Policy#, InsuredName, EffectiveDate,
etc.)-- the tables are CancelledHistory and CancelledNew. I need to run a
query weekly that will compare newly imported CancelledNew data against the
CancelledHistory data and somehow find out what policies are in the History
table that are not in the New table and what policies are in the New table
that aren't in the History table. I'm not concerned with the policies the 2
tables have in common.

Does anyone have any ideas on how to do this? Any help would be very
appreciated! As I'm in the beginning design stage, I'm able to switch from
two tables to one, etc., if there's a better way to do this. Thanks.

 

[John Spencer]

Have you looked at the Unmatched query wizard? It will build a query to
show you Items in TableA not in TableB and another to show items in TableB
not in TableA.

On the other hand, you could just set up a unique index on Policy# and
import the newly cancelled data into the cancelled table. When you do this,
you will get a warning message that "some number" of records could not be
imported due to conflicts. Those will be the Policy# records that already
exist in the table.

 

[ehale]

John,

Thanks for your help on this -- the unmatched query wizard worked great.