Hi Dolphy,
You can do this via a MakeTable query.
Open a new query, and add both TableA and TableB in the upper section of the
query design grid. Join each field of TableA to the corresponding field in
TableB; right-click on each join line and change the join property to
"Include all records from TableA and only those records from TableB where
the joined fields are equal". Drag the * field from TableA into the field
row of the query grid. Drag each field from TableB into the field row of
the query grid; for each of the fields from TableB, enter Is Null in the
first criteria row.
If you look at the SQL view of the query, it will be similar to this (with
your field names, and joins and criteria for every field from TableB):
SELECT TableA.*
FROM TableA LEFT JOIN TableB
ON (TableA.Field1 = TableB.Field1)
AND (TableA.Field2 = TableB.Field2)
WHERE (((TableB.Field2) Is Null)
AND ((TableB.Field1) Is Null));
If you run this query, it will show only those records from TableA which do
not have a matching record in TableB.
You can convert this query to a MakeTable query (via the Query menu). When
you do so, you will be prompted to enter the new table name; enter TableC
and run the query. And that's it. You will still have your existing TableA
and TableB, both with all their data, and a new TableC which contains only
those records from TableA which do not exist in TableB.
If you only want to remove records where certain fields are duplicated
(rather than every field), only join the fields of interest and only add
those fields from TableB (with the Is Null criteria) to the query design
grid.
HTH,
Rob
