Delete unmatched records

  • Thread starter Thread starter JS39714
  • Start date Start date
J

JS39714

I have 2 tables that contain the same data. One table has some deletions
made to it. I would either like to delete from the 2nd table at the saem
time, or know how to have records in the 2nd table deleted only if they are
not present in the 1st table.
Thank you for any help!
 
If the two tables share a common field key - you can use Referential
Integrity to do the delete for you.

Show Relationships and use the Show Table window for your two tables. Drag
the common field from one list and drop it on the same field in the other
table. Check Referential Integrity, check Delete Records option.
 
It is not giving me the ref. integ. or delete records options. They are
"grayed" out. My relationship type says Intermediate.
 
From your posting I would guess that you linked the two tables on their
primary keys. So Access has no good way to determine which table is the
"parent" and which table is the "Child".

You are probably going to have to use a delete query to delete the relevant
records.

If the number of records is small, you can use
DELETE
FROM TableA
WHERE TableA.PrimaryKey Not IN
(SELECT TableB.PrimaryK
FROM TableB)

Large number of records
DELETE
FROM TableA
WHERE TableA.PrimaryKey in (
SELECT TableA.PrimaryKey
FROM TableA LEFT JOIN TableB
ON TableA.PrimaryKey = TableB.PrimaryKey
WHERE TableB.PrimaryKey is Null)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top