<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 10 (filtered)">
<title>Message</title>
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Helvetica;
panose-1:2 11 6 4 2 2 2 2 2 4;}
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:"Comic Sans MS";
panose-1:3 15 7 2 3 3 2 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
h1
{margin-top:12.0pt;
margin-right:0in;
margin-bottom:3.0pt;
margin-left:0in;
page-break-after:avoid;
font-size:14.0pt;
font-family:Arial;
font-weight:bold;}
p.MsoHeader, li.MsoHeader, div.MsoHeader
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Times New Roman";}
p.MsoFooter, li.MsoFooter, div.MsoFooter
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Times New Roman";}
p.MsoList, li.MsoList, div.MsoList
{margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:.25in;
margin-bottom:.0001pt;
text-indent:-.25in;
font-size:11.0pt;
font-family:"Times New Roman";}
p.MsoTitle, li.MsoTitle, div.MsoTitle
{margin-top:12.0pt;
margin-right:0in;
margin-bottom:3.0pt;
margin-left:0in;
text-align:center;
font-size:16.0pt;
font-family:Arial;
font-weight:bold;}
p.MsoBodyText, li.MsoBodyText, div.MsoBodyText
{margin-top:0in;
margin-right:0in;
margin-bottom:6.0pt;
margin-left:0in;
font-size:11.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
p
{margin-right:0in;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman";}
p.jr1, li.jr1, div.jr1
{margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:.8in;
margin-bottom:.0001pt;
text-indent:-.3in;
font-size:10.0pt;
font-family:"Times New Roman";
font-weight:bold;
font-style:italic;}
p.jr2, li.jr2, div.jr2
{margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:.75in;
margin-bottom:.0001pt;
text-indent:-.25in;
font-size:12.0pt;
font-family:"Times New Roman";}
p.jr4, li.jr4, div.jr4
{margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:.25in;
margin-bottom:.0001pt;
text-indent:-.25in;
font-size:12.0pt;
font-family:"Times New Roman";}
span.EmailStyle26
{font-family:Helvetica;
color:blue;}
@page Section1
{size:8.5in 11.0in;
margin:.35in .35in .35in .35in;}
div.Section1
{page:Section1;}
/* List Definitions */
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 color=blue face=Helvetica><span
style='font-size:10.0pt;font-family:Helvetica;color:blue'>Yes, I did that for
all queries except the last one (#4) where I did not directly link the two
tables together.</span></font></p>
<p class=MsoNormal><font size=2 color=blue face=Helvetica><span
style='font-size:10.0pt;font-family:Helvetica;color:blue'> </span></font></p>
<div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'>
<p class=MsoNormal><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma'>-----Original Message-----<br>
<b><span style='font-weight:bold'>From:</span></b> Charlotte Foust
[mailto:cfoust@infostatsystems.com] <br>
<b><span style='font-weight:bold'>Sent:</span></b> Tuesday, June 10, 2003 4:54
PM<br>
<b><span style='font-weight:bold'>To:</span></b> accessd@databaseadvisors.com<br>
<b><span style='font-weight:bold'>Subject:</span></b> RE: [AccessD] Delete
Query problem</span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> </span></font></p>
<div>
<p class=MsoNormal><font size=2 color=blue face="Comic Sans MS"><span
style='font-size:10.0pt;font-family:"Comic Sans MS";color:blue'>Did you
remember to set the query's UniqueRecords property to Yes?</span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> </span></font></p>
</div>
<div>
<p class=MsoNormal><font size=2 color=blue face="Comic Sans MS"><span
style='font-size:10.0pt;font-family:"Comic Sans MS";color:blue'>Charlotte Foust</span></font></p>
</div>
<blockquote style='margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'>
<p class=MsoNormal style='margin-bottom:12.0pt'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>-----Original Message-----<br>
<b><span style='font-weight:bold'>From:</span></b> Robinson, Joyce
[mailto:JoyceRobinson@oasas.state.ny.us] <br>
<b><span style='font-weight:bold'>Sent:</span></b> Tuesday, June 10, 2003 12:26
PM<br>
<b><span style='font-weight:bold'>To:</span></b> AccessD@databaseadvisors.com<br>
<b><span style='font-weight:bold'>Subject:</span></b> [AccessD] Delete Query
problem</span></font></p>
<p><font size=2 face="Times New Roman"><span style='font-size:10.0pt'><!-- Converted from text/plain format -->Hi,</span></font>
<br>
<font size=2><span style='font-size:10.0pt'> </span></font> <br>
<font size=2><span style='font-size:10.0pt'>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. </span></font></p>
<p><font size=3 face="Times New Roman"><span style='font-size:12.0pt'><br>
</span></font><font size=2><span style='font-size:10.0pt'>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.</span></font></p>
<p><font size=3 face="Times New Roman"><span style='font-size:12.0pt'><br>
</span></font><font size=2><span style='font-size:10.0pt'>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.</span></font></p>
<p><font size=3 face="Times New Roman"><span style='font-size:12.0pt'><br>
</span></font><font size=2><span style='font-size:10.0pt'>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.</span></font></p>
<p><font size=3 face="Times New Roman"><span style='font-size:12.0pt'><br>
</span></font><font size=2><span style='font-size:10.0pt'>Attempt #1 (using
>From “Clients” right join Q_DropOuts ):</span></font> <br>
<font size=2><span style='font-size:10.0pt'>DELETE DISTINCTROW Clients.*,
Clients.[CID#]</span></font> <br>
<font size=2><span style='font-size:10.0pt'>FROM Clients RIGHT JOIN
[Q_DropOuts] ON Clients.[CID#] = [Q_DropOuts].[CID#_Link]</span></font> <br>
<font size=2><span style='font-size:10.0pt'>WHERE (((Clients.[CID#]) Is Not
Null));</span></font> <br>
<font size=2><span style='font-size:10.0pt'> </span></font> <br>
<font size=2><span style='font-size:10.0pt'>Attempt #2 (using left join from
DropOut query and a non-linking field in Client table as “not null”
criteria):</span></font> <br>
<font size=2><span style='font-size:10.0pt'>DELETE DISTINCTROW Clients.*,
Clients.ID</span></font> <br>
<font size=2><span style='font-size:10.0pt'>FROM [Q_DropOuts] LEFT JOIN Clients
ON [Q_DropOuts].[CID#_Link] = Clients.[CID#]</span></font> <br>
<font size=2><span style='font-size:10.0pt'>WHERE (((Clients.ID) Is Not Null));</span></font>
<br>
<font size=2><span style='font-size:10.0pt'> </span></font> <br>
<font size=2><span style='font-size:10.0pt'>Attempt #3a and 3b (using inner
joins):</span></font> <br>
<font size=2><span style='font-size:10.0pt'>DELETE DISTINCTROW Clients.*</span></font>
<br>
<font size=2><span style='font-size:10.0pt'>FROM [Q_DropOuts] INNER JOIN
Clients ON [Q_DropOuts].[CID#_Link] = Clients.[CID#];</span></font> <br>
<font size=2><span style='font-size:10.0pt'> </span></font> <br>
<font size=2><span style='font-size:10.0pt'>DELETE DISTINCTROW Clients.*</span></font>
<br>
<font size=2><span style='font-size:10.0pt'>FROM Clients INNER JOIN
[Q_DropOuts] ON Clients.[CID#] = [Q_DropOuts].[CID#_Link];</span></font> <br>
<font size=2><span style='font-size:10.0pt'> </span></font> <br>
<font size=2><span style='font-size:10.0pt'> </span></font> <br>
<font size=2><span style='font-size:10.0pt'>Attempt #4 (using tables and
eliminating query as datasource; reformatted linking field directly in
query)</span></font> <br>
<font size=2><span style='font-size:10.0pt'>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]</span></font></p>
<p><font size=2 face="Times New Roman"><span style='font-size:10.0pt'>FROM
Clients, [DroppedOut Clients_OL]</span></font> <br>
<font size=2><span style='font-size:10.0pt'>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#]));</span></font></p>
<p><font size=3 face="Times New Roman"><span style='font-size:12.0pt'><br>
</span></font><font size=2><span style='font-size:10.0pt'> </span></font> <br>
<font size=2><span style='font-size:10.0pt'>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). </span></font></p>
<p><font size=3 face="Times New Roman"><span style='font-size:12.0pt'><br>
</span></font><font size=2><span style='font-size:10.0pt'>Thanks very much for
any help!</span></font> </p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> </span></font></p>
<p><font size=2 face="Times New Roman"><span style='font-size:10.0pt'>NOTE:
Sorry if this is a duplicate message. I tried to cancel previous
oversized version.</span></font> </p>
<p><font size=3 face="Times New Roman"><span style='font-size:12.0pt'><br>
</span></font><font size=2><span style='font-size:10.0pt'>======Access2002,
WinXP Profl</span></font> <br>
<font size=2><span style='font-size:10.0pt'> </span></font> </p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> </span></font></p>
<p><font size=2 face="Times New Roman"><span style='font-size:10.0pt'>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~</span></font>
<br>
<font size=2><span style='font-size:10.0pt'>Joyce Robinson [Research Scientist]</span></font>
<br>
<font size=2><span style='font-size:10.0pt'>NYS OASAS - Evaluation
and Program Monitoring</span></font> <br>
<font size=2><span style='font-size:10.0pt'>Albany, NY </span></font><br>
<font size=2><span style='font-size:10.0pt'>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~</span></font>
<br>
<font size=2><span style='font-size:10.0pt'> </span></font> <br>
<font size=2><span style='font-size:10.0pt'> </span></font> <br>
<font size=2><span style='font-size:10.0pt'> </span></font> </p>
</blockquote>
</div>
</div>
</body>
</html>