<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Message</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2800.1170" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=972525320-10062003><FONT face="Comic Sans MS" color=#0000ff
size=2>Did you remember to set the query's UniqueRecords property to
Yes?</FONT></SPAN></DIV>
<DIV><SPAN class=972525320-10062003><FONT face="Comic Sans MS" color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=972525320-10062003><FONT face="Comic Sans MS" color=#0000ff
size=2>Charlotte Foust</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B> Robinson, Joyce
[mailto:JoyceRobinson@oasas.state.ny.us] <BR><B>Sent:</B> Tuesday, June 10,
2003 12:26 PM<BR><B>To:</B> AccessD@databaseadvisors.com<BR><B>Subject:</B>
[AccessD] Delete Query problem<BR><BR></FONT></DIV><!-- Converted from text/plain format -->
<P><FONT size=2>Hi,</FONT> <BR><FONT size=2> </FONT> <BR><FONT size=2>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. </FONT></P>
<P><FONT size=2></FONT> <BR><FONT size=2>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.</FONT></P>
<P><FONT size=2></FONT> <BR><FONT size=2>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.</FONT></P>
<P><FONT size=2></FONT> <BR><FONT size=2>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.</FONT></P>
<P><FONT size=2></FONT> <BR><FONT size=2>Attempt #1 (using From “Clients”
right join Q_DropOuts ):</FONT> <BR><FONT size=2>DELETE DISTINCTROW Clients.*,
Clients.[CID#]</FONT> <BR><FONT size=2>FROM Clients RIGHT JOIN [Q_DropOuts] ON
Clients.[CID#] = [Q_DropOuts].[CID#_Link]</FONT> <BR><FONT size=2>WHERE
(((Clients.[CID#]) Is Not Null));</FONT> <BR><FONT size=2> </FONT>
<BR><FONT size=2>Attempt #2 (using left join from DropOut query and a
non-linking field in Client table as “not null” criteria):</FONT> <BR><FONT
size=2>DELETE DISTINCTROW Clients.*, Clients.ID</FONT> <BR><FONT size=2>FROM
[Q_DropOuts] LEFT JOIN Clients ON [Q_DropOuts].[CID#_Link] =
Clients.[CID#]</FONT> <BR><FONT size=2>WHERE (((Clients.ID) Is Not
Null));</FONT> <BR><FONT size=2> </FONT> <BR><FONT size=2>Attempt #3a and
3b (using inner joins):</FONT> <BR><FONT size=2>DELETE DISTINCTROW
Clients.*</FONT> <BR><FONT size=2>FROM [Q_DropOuts] INNER JOIN Clients ON
[Q_DropOuts].[CID#_Link] = Clients.[CID#];</FONT> <BR><FONT
size=2> </FONT> <BR><FONT size=2>DELETE DISTINCTROW Clients.*</FONT>
<BR><FONT size=2>FROM Clients INNER JOIN [Q_DropOuts] ON Clients.[CID#] =
[Q_DropOuts].[CID#_Link];</FONT> <BR><FONT size=2> </FONT> <BR><FONT
size=2> </FONT> <BR><FONT size=2>Attempt #4 (using tables and eliminating
query as datasource; reformatted linking field directly in query)</FONT>
<BR><FONT size=2>DELETE Clients.*, Clients.ID, [DroppedOut Clients_OL].[Sex]
& CStr(Format([DroppedOut Clients_OL].[Birth Date],"mmddyyyy")) &
Format([DroppedOut Clients_OL].[Last 4 Ssn],"0000") & [L Name 2 Char] AS
[CID#_Link]</FONT></P>
<P><FONT size=2>FROM Clients, [DroppedOut Clients_OL]</FONT> <BR><FONT
size=2>WHERE (((Clients.ID) Is Not Null) AND (([DroppedOut Clients_OL].[Sex]
& CStr(Format([DroppedOut Clients_OL].[Birth Date],"mmddyyyy")) &
Format([DroppedOut Clients_OL].[Last 4 Ssn],"0000") & [L Name 2
Char])=[Clients].[CID#]));</FONT></P>
<P><FONT size=2></FONT> <BR><FONT size=2> </FONT> <BR><FONT size=2>Also,
I will also be doing 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). </FONT></P>
<P><FONT size=2></FONT> <BR><FONT size=2>Thanks very much for any help!</FONT>
</P><BR>
<P><FONT size=2>NOTE: Sorry if this is a duplicate message.
I tried to cancel previous oversized version.</FONT> </P>
<P><FONT size=2></FONT> <BR><FONT size=2>======Access2002, WinXP Profl</FONT>
<BR><FONT size=2> </FONT> </P><BR>
<P><FONT size=2>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~</FONT> <BR><FONT
size=2>Joyce Robinson [Research Scientist]</FONT> <BR><FONT size=2>NYS
OASAS - Evaluation and Program Monitoring</FONT> <BR><FONT
size=2>Albany, NY </FONT><BR><FONT
size=2>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~</FONT> <BR><FONT
size=2> </FONT> <BR><FONT size=2> </FONT> <BR><FONT
size=2> </FONT> </P></BLOCKQUOTE></BODY></HTML>