Deleting associated data across multiple tables

[seawulf]

I have two tables, one contains customer data, and when we contact our customers we save call history notes to another table. I'd like to remove customer data from the primary table and somehow delete all associated call history notes from the other table.



I've been asked to do this but when I google for instructions it makes my head want to explode and I know there is an easy way to delete this data because no one would use access if you had to do it one at a time.

Any help would be appreciated!

[pbaldy]

You can set up cascade deletes in referential integrity in the relationships window. Or if you have a delete button on a form, you can run a delete query that deletes data from the calls table using the customer on the form as a criteria, then let it delete the customer. The first is more "bullet proof", as no matter how a user might delete a customer, the calls will get deleted.

[Bulzie]

Use Relationships. Database Tools> Relationships, Add your 2 tables and link the connecting fields (CustomerID maybe?). When box comes up Select to Enforce Referential Integrity and select Update and Delete check boxes. Then when you delete a Customer in the main table, all notes from history table will be deleted for that Customer.

[seawulf]

This did the trick! I get sick of following really terrible instructions I find on the web that never answer what I know are simple(at least not difficult) questions. Your help here today is really direct and refreshing. Thanks!

[NightWalker]

Why would you want to delete items like that out of a DB. You will lose all ability to track historical data. I am not understanding why someone would do that.