Delete Query problem 1

[robinsj2564]

Hi,

Any help would be greatly appreciated. I am trying unsuccessfully to run a Delete Query and keep getting the fairly common response “Could not delete from specified tables.” I have reviewed many similar posts and still cannot seem to get mine to work.


I have 2 tables named “Clients” (larger table) and “DropOuts” (smaller table). Some (but not all) clients in “Dropouts” table are also in the “Clients” table. I want to delete all client records from the “Clients” table that are also found in the smaller “DropOuts” table. There is a one-to-one relationship between these tables.

In most of my attempted delete queries, I have included the “Clients” table and a query of the ‘DropOuts’ data (called ‘Q_DropOuts’). I used a query for DropOuts because the DropOut data came from a different data system and the linking field [CID#_Link] needed to be modified in order to match with the CID# from “Clients” table.

Several Attempts so far result in the right records in a Select Query view but results in “Could not delete….” message when I run it as a Delete Query.

I have tried several variations on the 2 approaches below:

DELETE DISTINCTROW Clients.*, Clients.[CID#]
FROM Clients RIGHT JOIN [Q_DropOuts] ON Clients.[CID#] = [Q_DropOuts].[CID#_Link]
WHERE (((Clients.[CID#]) Is Not Null));

[ALSO]

DELETE DISTINCTROW Clients.*
FROM [Q_DropOuts] INNER JOIN Clients ON [Q_DropOuts].[CID#_Link] = Clients.[CID#];


Also, I hope to do a variation on this for a similar database, but where the “Client Interviews” table will contain one or more client records (i.e., where each record is really a client interview). Any client in the “DropOuts” table (or query) will have one or more records deleted from the “Client Interviews” table (where each client may be administered one or more interviews).

Thanks very much for any help!



Joyce Robinson
Research Scientist, NYS-OASAS

 

[mpastore]

Hi Joyce,

Try

delete from clients where [cid#]
in(select [cid#_link] from q_dropouts)



Mike Pastore

Hats off to (Roy) Harper

 

[scriverb]

You can't use any type of a Join in a Delete query. ACCESS just can't handle trying to figure out which table to delete from. There are two possibile options.

Delete A.*
FROM Clients as A
WHERE A.[CID#] IN (Select B.[CID#_Link] FROM [Q_DropOuts] as B)

The downside of this technique is that if the table [Q_DropOuts] is very very large then this is slow. You see for every record in Clients the sub query executes one time.

The other way is to perform an innter join and update a field in the table Clients with a flag(i.e. 99999 ) where you can then run a Delete query selecting on the flag and deleting all that have 99999 in that field.

If you need help with those queries I can be of assistance if you choose that route. Let me know.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[robinsj2564]

Thanks so much to both Mike and Bob for your very helpful suggestions. I can't tell you how much time I have spent.... Actually, all of your suggestions worked great! For my purposes, I'll probably go with Mike's approach. My final syntax was:
DELETE Clients.*, Clients.[CID#]
FROM Clients
WHERE (((Clients.[CID#]) In (Select [Cid#_link] from Q_Dropouts)));


Thanks very much for all of your help!




Joyce Robinson
Research Scientist, NYS-OASAS

 

[mpastore]

Way to go Joyce...

Mike Pastore

Hats off to (Roy) Harper