Delete records from one table that appear in another table

[USA_Chancer]

Hi all,

Quick question...

I have a master client table and a table of clients that have ordered from us, so don't want to be called again.

I want to delete the clients record from the master table by comparing the two tables and sayi that 'everytime you find a phone number from the 'ordered' table that exists in the master table, delete the record from the master table.

That way I will end up with a master table of clients who haven't placed an order.

Could anyone tell me the quey to run that could do that.

Thanks in advance - Lee

 

[KeithG]

This SQL statement should work for you when you change the table names to valid tables.

tblMain= Your Master table
tblSub= Your Other table

DELETE tblMain.*, tblMain.[Phone Number]
FROM tblMain
WHERE (((tblMain.[Phone Number]) In (Select tblSub.[Phone Number] from tblSub)));

 

[USA_Chancer]

Thanks Keith

Thanks for your reply Keith...Can you confirm this is what I should be doing...

New Query
Add the two tables to the query; tblMain and tblSub
Change the type of query to 'Delete Query'
Grab the tblMain * to the query grid
Grab the field 'Phone Number' from the tblSub to the query grid
Under the word FROM in the criteria line add: DELETE tblMain.*, tblMain.[Phone Number]
Under the word WHERE in the criteria line add: ((([tblMain].[Phone Number]) In (Select tblSub.[Phone Number] from tblSub)))

I am doing something wrong becasue as soon as I click out of the criteria area for the FROM code it says I have entered an incorrect . or !

Sorry to be such a dope!

 

[USA_Chancer]

Got it!

Thanks Keith,

Instead of trying to put it on the query grid, I found 'SQL Specific' under the 'query' drop down menu and simply cut and pasted your code, saved the query and ran it and it worked fine.

Thanks for the help.

Kind regards - Lee

 

[neileg]

Pleased that worked for you. However I would suggest that this is not the correct way to deal with this. As soon as a customer has ordered from you, your main table is out of date, unless you keep running the delete query. It would be better to use a select query that returned all the records in the main table where there is no matching record in you other table. Use this query as the basis for your users and it will be permanently up to date as soon as you enter the data in for the orders.

The unmatched query wizard in Access will help you build this query.