Delete Query

[SpindleMania]

Hi All,

i have a Query that references 2 tables, a customers table and a purchase table.

i have a query that looks up customers who have not purchased within the last 7 months.

i would like to make a seperate form / query / macro / script or whatever that will prompt the user to enter the primary key from the customer table (customers unique number) and then it delete that customers record from the customers table and not touch anything from the purchases table.

i have looked at a delete query but can't make it so i can run my criteria and not delete from the purchases table

can anyone help?

 

[neileg]

So you're going to be left with records in the purchases table where the FK link to customers is broken? It would make much more sense to leave the customers in the table but flag them as inactive.

 

[SpindleMania]

Hi Neileg,

the purchases table is not a direct link to the customers table as all that is recirded is the customers number, which is manually entered.

it is the query that links the two tables as it has to reference the customers last purchase date to find which customers havent purchased within 7 months and it matches the customer number with the customer details tabel to show their name on the query.

i have set a form command button that allows me to delete records from the customer table and doesn't touch the purchases tabel but i have around 300 to do so it will take a while!!

 

[neileg]

It's your database, you do what you want. However:
1) I certainly wouldn't expect users to have to manually enter the customer number, I would expect them to choose this from a combo box
2) Once your customer data is deleted it's gone. I don't see the need to delete it. One of the best sources for new sales is old customers.
3) If you really want to delete these records I would create the inactive customer query as in your other thread, use this to populate a combo box on a form so the customer can be selected and run a delete query based on the combo box selection.
4) I suspect you want to do all the deletes at once. In which case I would use you dead customer query to feed a delete query.