orphaned records

  • Thread starter Thread starter Tina
  • Start date Start date
By "orphaned records", do you mean:
A) records in the related table where the foreign key is null, or
B) records in the main table that have no matching records in the related
table.

A) is very easy. Just:
1. Create a query into the related table.
2. Drag the foreign key field into the grid.
3. In the Criteria row under this field, enter:
Is Null
4. Drag the * into the grid.
5. Check that the query is returning the correct records.
6. Change it to a Delete query (Delete on Query menu)
7. Run the query.

B) is more difficult. Access does not like deleting when there are multiple
tables and outer joins, so try a subquery.
1. Create a query into just the main table.
2. In the next column, type your subquery. This kind of thing:
EXISTS (SELECT MyForeignKey From MyRelatedTable
WHERE MyRelatedTable.MyForeignKey = MyMainTable.MyMainID)
3. In the Criteria row beneath this field, enter:
False
4. Drag the * into the grid.
5. Check that the query is returning the correct records.
6. Change it to a Delete query (Delete on Query menu).
7. Run the query.

In the end, the delete query will look something like this:
DELETE FROM MyMainTable
WHERE NOT EXISTS
(SELECT MyForeignKey From MyRelatedTable
WHERE MyRelatedTable.MyForeignKey = MyMainTable.MyMainID);
 
Tina said:
Acc2003

How can I delete orphaned records between two tables?
Click to expand...


If you'd use referential integrity on the relationship
between the two tables, you would be able to create orphans.

To identify the orphans, use a frustrated outer join query

qryOrphans:
SELECT orphantable.*
FROM orphantable LEFT JOIN parenttable
ON orphantable.foreignkey = parenttable.primarykey
WHERE parenttable.primarykey Is Null

You can then use that to delete the orphans
DELETE *
FROM orphantable
WHERE foreignkey IN(SELECT foreignkey FROM qryOrphans)
 
Back
Top