[AccessD] Delete Query problem

Gustav,

Thanks so much for your very helpful modification.  I greatly appreciate your follow-up response.  Obviously, I'm not a programmer and have quite a bit of learning to do. At least now, I can get rid of those pesty Dropouts. In the meantime, I will work on my future field names.   :)  


> -----Original Message-----
> From: Gustav Brock [mailto:gustav at cactus.dk]
> Sent: Wednesday, June 11, 2003 2:41 PM
> To: accessd at databaseadvisors.com
> Subject: Re: [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.
> 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com