Problem with Delete Query

[BionicKate]

In my access database i have an Append Query which copies data on Customers and their Orders from three related tables to one new table (tblOldCustomers/Orders).

However i don't know how to create a delete query which will erase the inromation from the original three related tables.(Customer, Customer Orders, Range). As the query is based on one date which is in the Customer orders table, its not possible to use three seperate delete queries!

any ideas?

Kate

 

[Pat Hartman]

You have accidentally created two threads, please delete the other one before someone replies to it. Open it, and select edit. You will be given the option to delete.

You need to establish proper relationships between the tables so that you can choose the enforce Referential Integrity option and then for the order and the order details relationship, the cascade delete option. You don't want to specify cascade delete for the customer --> orders relationship because you don't want to accidentally delete current order data. Therefore, you want RI to prevent you from deleting a customer as long as there is a related order for that customer. Then once you have archived all of a customer's orders and deleted them, you will be allowed to delete the customer record.

I presume that you don't really want to delete a customer justbecause you have archived his order so you would simply run a query that deletes the order. When you delete the order, the details in the order details table (or whatever you have called it) will also be deleted since you have specified cascade delete in their relationship.

 

[nicwnacw]

delete query

when I have to do this type of thing I check that the append (or update ) query works. If it does I copy it and change it to a dlete query. I f you try this I suggest that you do it on a copy of the database.