[AccessD] Delete Query problem

Hi Joyce

Hmm ... those fieldnames are awful. Crosses, "#", are used for
wrapping dates so these must be bracketed.

Anyway, this should do:

  DELETE * FROM Clients
  WHERE Clients.[CID#] =
    (SELECT Q_DropOuts.[CID#_Link] FROM Q_DropOuts
    WHERE Q_DropOuts.[CID#_Link] = Clients.[CID#];);

/gustav


> Thanks Gustav.  I tried your approach as best I could but it still does not seem to be working.  I used the following [exact] syntax:

> DELETE * FROM Clients WHERE CID# =(SELECT CID#_Link FROM Q_DropOuts WHERE CID#_Link =Clients.CID#;);


> I got the error message:  
>         Syntax Error [missing operator] in query expression 'CID# = (SELECT     CID#_Link FROM Q_DropOuts WHERE CID#_Link =Clients.CID#;);

> I tried also adding brackets around the field names and 'Client' table and query 'Q_DropOuts' but that didn't help either.  I'm obviously still doing something wrong.


>> -----Original Message-----
>> From: Gustav Brock [mailto:gustav at cactus.dk]
>> Sent: Wednesday, June 11, 2003 7:12 AM
>> To: accessd at databaseadvisors.com
>> Subject: Re: [AccessD] Delete Query problem
>> 
>> Hi Joyce
>> 
>> Use a subquery to locate those clients:
>> 
>> <SQL>
>> 
>>   DELETE
>>     *
>>   FROM
>>     tblClients
>>   WHERE
>>     ID =
>>       (SELECT
>>         ID
>>       FROM
>>         tblDropOuts
>>       WHERE
>>         ID = tblClients.ID;);
>> 
>> </SQL>
>> 
>> /gustav
>> 
>> 
>> > 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.