Deleting "Rogue" sub-table entries

[Guest]

Let's say I have a sub-table (Table B) that is linked to my main table (Table
A) in a many-to-one relationship. The tables are linked via "Parent ID" in
Table B and "ID" in Table A. Since I did not enforce referential integrity
in the relationship from the beginning, Table B has entries that do not have
a parent in Table A because the record in Table A has been deleted.

How can I design a query to show me how many "rogue" entries there are in
Table B (Select) and then a query to delete only those rogue entries (Delete)?

Also, can I then turn on referential integrity so that in the future when I
delete a record from the main table, any linked items in the sub-table will
be deleted?

Thanks,

Eric

 

[Guest]

Based on my research of other postings here, I tried this Select query and it
returned nothing, even though I know the "rogue" records are there:

SELECT [Table B].*
FROM [Table A] INNER JOIN [Table B] ON [Table A].[ID] = [Table B].[Parent ID]
WHERE ((([Table B].[Parent ID]) Not In ([Table A]![ID])));

 

[mosleyn]

Based on my research of other postings here, I tried this Select query and it
returned nothing, even though I know the "rogue" records are there:

SELECT [Table B].*
FROM [Table A] INNER JOIN [Table B] ON [Table A].[ID] = [Table B].[Parent ID]
WHERE ((([Table B].[Parent ID]) Not In ([Table A]![ID])));

That's almost correct. However, by using an INNER JOIN, you're
telling the query to only show records that are equal in both Table A
and Table B. If you use a RIGHT JOIN and change your subquery so that
it says ((([Table B].[Parent ID]) Not In (SELECT [Table A].[ID] FROM
[Table A])) , you might have better luck.

 

[Marshall Barton]

Let's say I have a sub-table (Table B) that is linked to my main table (Table
A) in a many-to-one relationship. The tables are linked via "Parent ID" in
Table B and "ID" in Table A. Since I did not enforce referential integrity
in the relationship from the beginning, Table B has entries that do not have
a parent in Table A because the record in Table A has been deleted.

How can I design a query to show me how many "rogue" entries there are in
Table B (Select) and then a query to delete only those rogue entries (Delete)?

Also, can I then turn on referential integrity so that in the future when I
delete a record from the main table, any linked items in the sub-table will
be deleted?

The unmatched query wizard should create a query like:

SELECT [Table B].[Parent ID]
FROM [Table B] LEFT JOIN [Table A]
ON [Table B].[Parent ID] = [Table A].ID
WHERE [Table A].ID Is Null

You should also be able to use the Query - Delete menu item
to change it to a DELETE query.

Yes, after the orphan records have been deleted, you can use
the Relationships window to enforce referential integrity
and then set cascade deletes.

 

[Guest]

Thanks! That did the trick. Although I also discovered some records in the
sub-table that have Null entries for [Parent ID], so I'll have to add that
possibility to the query as well.