<!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>
<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 vLink=purple link=blue>
<DIV><SPAN class=710023621-10062003><FONT face="Comic Sans MS" color=#0000ff
size=2>Huh? If you didn't directly link the two tables, the result will be
a cartesian product and you can't do a delete on that.</FONT></SPAN></DIV>
<DIV><SPAN class=710023621-10062003><FONT face="Comic Sans MS" color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=710023621-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 1:25 PM<BR><B>To:</B> accessd@databaseadvisors.com<BR><B>Subject:</B> RE:
[AccessD] Delete Query problem<BR><BR></FONT></DIV>
<DIV class=Section1>
<P class=MsoNormal><FONT face=Helvetica color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Helvetica">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 face=Helvetica color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Helvetica"></SPAN></FONT> </P>
<DIV
style="BORDER-RIGHT: medium none; PADDING-RIGHT: 0in; BORDER-TOP: medium none; PADDING-LEFT: 4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: blue 1.5pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: medium none">
<P class=MsoNormal><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; 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 face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<DIV>
<P class=MsoNormal><FONT face="Comic Sans MS" color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Comic Sans MS'">Did you
remember to set the query's UniqueRecords property to
Yes?</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal><FONT face="Comic Sans MS" color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Comic Sans MS'">Charlotte
Foust</SPAN></FONT></P></DIV>
<BLOCKQUOTE style="MARGIN-TOP: 5pt; MARGIN-BOTTOM: 5pt; MARGIN-RIGHT: 0in">
<P class=MsoNormal style="MARGIN-BOTTOM: 12pt"><FONT face=Tahoma
size=2><SPAN style="FONT-SIZE: 10pt; 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 face="Times New Roman" size=2><SPAN style="FONT-SIZE: 10pt"><!-- Converted from text/plain format -->Hi,</SPAN></FONT>
<BR><FONT size=2><SPAN style="FONT-SIZE: 10pt"> </SPAN></FONT>
<BR><FONT size=2><SPAN style="FONT-SIZE: 10pt">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 face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><BR></SPAN></FONT><FONT size=2><SPAN
style="FONT-SIZE: 10pt">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 face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><BR></SPAN></FONT><FONT size=2><SPAN
style="FONT-SIZE: 10pt">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 face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><BR></SPAN></FONT><FONT size=2><SPAN
style="FONT-SIZE: 10pt">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 face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><BR></SPAN></FONT><FONT size=2><SPAN
style="FONT-SIZE: 10pt">Attempt #1 (using >From “Clients” right join
Q_DropOuts ):</SPAN></FONT> <BR><FONT size=2><SPAN
style="FONT-SIZE: 10pt">DELETE DISTINCTROW Clients.*,
Clients.[CID#]</SPAN></FONT> <BR><FONT size=2><SPAN
style="FONT-SIZE: 10pt">FROM Clients RIGHT JOIN [Q_DropOuts] ON
Clients.[CID#] = [Q_DropOuts].[CID#_Link]</SPAN></FONT> <BR><FONT
size=2><SPAN style="FONT-SIZE: 10pt">WHERE (((Clients.[CID#]) Is Not
Null));</SPAN></FONT> <BR><FONT size=2><SPAN
style="FONT-SIZE: 10pt"> </SPAN></FONT> <BR><FONT size=2><SPAN
style="FONT-SIZE: 10pt">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: 10pt">DELETE DISTINCTROW Clients.*,
Clients.ID</SPAN></FONT> <BR><FONT size=2><SPAN style="FONT-SIZE: 10pt">FROM
[Q_DropOuts] LEFT JOIN Clients ON [Q_DropOuts].[CID#_Link] =
Clients.[CID#]</SPAN></FONT> <BR><FONT size=2><SPAN
style="FONT-SIZE: 10pt">WHERE (((Clients.ID) Is Not Null));</SPAN></FONT>
<BR><FONT size=2><SPAN style="FONT-SIZE: 10pt"> </SPAN></FONT>
<BR><FONT size=2><SPAN style="FONT-SIZE: 10pt">Attempt #3a and 3b (using
inner joins):</SPAN></FONT> <BR><FONT size=2><SPAN
style="FONT-SIZE: 10pt">DELETE DISTINCTROW Clients.*</SPAN></FONT> <BR><FONT
size=2><SPAN style="FONT-SIZE: 10pt">FROM [Q_DropOuts] INNER JOIN Clients ON
[Q_DropOuts].[CID#_Link] = Clients.[CID#];</SPAN></FONT> <BR><FONT
size=2><SPAN style="FONT-SIZE: 10pt"> </SPAN></FONT> <BR><FONT
size=2><SPAN style="FONT-SIZE: 10pt">DELETE DISTINCTROW
Clients.*</SPAN></FONT> <BR><FONT size=2><SPAN style="FONT-SIZE: 10pt">FROM
Clients INNER JOIN [Q_DropOuts] ON Clients.[CID#] =
[Q_DropOuts].[CID#_Link];</SPAN></FONT> <BR><FONT size=2><SPAN
style="FONT-SIZE: 10pt"> </SPAN></FONT> <BR><FONT size=2><SPAN
style="FONT-SIZE: 10pt"> </SPAN></FONT> <BR><FONT size=2><SPAN
style="FONT-SIZE: 10pt">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: 10pt">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 face="Times New Roman" size=2><SPAN style="FONT-SIZE: 10pt">FROM
Clients, [DroppedOut Clients_OL]</SPAN></FONT> <BR><FONT size=2><SPAN
style="FONT-SIZE: 10pt">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 face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><BR></SPAN></FONT><FONT size=2><SPAN
style="FONT-SIZE: 10pt"> </SPAN></FONT> <BR><FONT size=2><SPAN
style="FONT-SIZE: 10pt">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 face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><BR></SPAN></FONT><FONT size=2><SPAN
style="FONT-SIZE: 10pt">Thanks very much for any help!</SPAN></FONT> </P>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<P><FONT face="Times New Roman" size=2><SPAN
style="FONT-SIZE: 10pt">NOTE: Sorry if this is a duplicate
message. I tried to cancel previous oversized version.</SPAN></FONT>
</P>
<P><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><BR></SPAN></FONT><FONT size=2><SPAN
style="FONT-SIZE: 10pt">======Access2002, WinXP Profl</SPAN></FONT>
<BR><FONT size=2><SPAN style="FONT-SIZE: 10pt"> </SPAN></FONT> </P>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<P><FONT face="Times New Roman" size=2><SPAN
style="FONT-SIZE: 10pt">~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~</SPAN></FONT>
<BR><FONT size=2><SPAN style="FONT-SIZE: 10pt">Joyce Robinson [Research
Scientist]</SPAN></FONT> <BR><FONT size=2><SPAN style="FONT-SIZE: 10pt">NYS
OASAS - Evaluation and Program Monitoring</SPAN></FONT>
<BR><FONT size=2><SPAN style="FONT-SIZE: 10pt">Albany, NY
</SPAN></FONT><BR><FONT size=2><SPAN
style="FONT-SIZE: 10pt">~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~</SPAN></FONT>
<BR><FONT size=2><SPAN style="FONT-SIZE: 10pt"> </SPAN></FONT>
<BR><FONT size=2><SPAN style="FONT-SIZE: 10pt"> </SPAN></FONT>
<BR><FONT size=2><SPAN style="FONT-SIZE: 10pt"> </SPAN></FONT>
</P></BLOCKQUOTE></DIV></DIV></BLOCKQUOTE></BODY></HTML>