Deleting records

[Dudley]

I wish to delete Clients which have had no invoices.

I have field 'Customer Code' in both the Clients and Invoices tables, so I
should be able to delete records in Clients when Customer Code in Invoices is
null, but I cannot work out how to do it.

Can anyone advise please.

Thanks
Dudley

 

[Allen Browne]

Try a query like this:

DELETE FROM Clients
WHERE NOT EXISTS
(SELECT [Customer Code]
FROM Invoices
WHERE Invoices.[Customer Code] = Clients.[Customer Code]);

 

[Dudley]

Thanks very much. That worked fine.

I wanted to keep a backup copy of deleted records, so before I ran your code
I copied the Clients table as OldClients, and adapted your code to delete new
clients as follows (as amended by Access)

DELETE *
FROM OldClients
WHERE (SELECT [Customer Code]
FROM Invoices
WHERE Invoices.[Customer Code] = OldClients.[Customer Code]);

I get a message that the database engine cannot find table OldClients. Can
you advise why? I have checked carefully for typos.

Thanks very much
Dudley

Try a query like this:

DELETE FROM Clients
WHERE NOT EXISTS
(SELECT [Customer Code]
FROM Invoices
WHERE Invoices.[Customer Code] = Clients.[Customer Code]);

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I wish to delete Clients which have had no invoices.

I have field 'Customer Code' in both the Clients and Invoices tables, so I
should be able to delete records in Clients when Customer Code in Invoices
is
null, but I cannot work out how to do it.

 

[Allen Browne]

It will probably work after a re-start (discovering the renamed table.)

Either that or the name is wrong (e.g. an extra space) or else Name
AutoCorrect is intereferring:
http://allenbrowne.com/bug-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks very much. That worked fine.

I wanted to keep a backup copy of deleted records, so before I ran your
code
I copied the Clients table as OldClients, and adapted your code to delete
new
clients as follows (as amended by Access)

DELETE *
FROM OldClients
WHERE (SELECT [Customer Code]
FROM Invoices
WHERE Invoices.[Customer Code] = OldClients.[Customer Code]);

I get a message that the database engine cannot find table OldClients. Can
you advise why? I have checked carefully for typos.

Thanks very much
Dudley

Try a query like this:

DELETE FROM Clients
WHERE NOT EXISTS
(SELECT [Customer Code]
FROM Invoices
WHERE Invoices.[Customer Code] = Clients.[Customer Code]);

I wish to delete Clients which have had no invoices.

I have field 'Customer Code' in both the Clients and Invoices tables,
sp
I should be able to delete records in Clients when Customer Code in
Invoices is null, but I cannot work out how to do it.