<!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;}
span.EmailStyle27
{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=339543522-10062003><FONT face="Comic Sans MS" color=#0000ff
size=2>Sure you can include a query as one of the data sources, at least as long
as it's being used to determine the matching records for the delete rather than
being the object that's the target of the deletes.</FONT></SPAN></DIV>
<DIV><SPAN class=339543522-10062003><FONT face="Comic Sans MS" color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=339543522-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:47 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: 11pt; COLOR: blue; FONT-FAMILY: Helvetica">I cannot link the
two tables directly since I had to modify the linking field from the DropOut
data in order to match CID# in the Clients table. Is it then not
possible to do this without first running a make-table query to create a
revised DropOut table? That is, can you not include a query as one of
the data sources in a delete query?</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>
<P class=MsoNormal><FONT face=Helvetica color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Helvetica">Joyce
Robinson</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>
<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> </SPAN></FONT><FONT face=Tahoma
size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">Tuesday, June 10,
2003</SPAN></FONT><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma"> </SPAN></FONT><FONT face=Tahoma
size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">5:37
PM</SPAN></FONT><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma"><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'">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.</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 1:25
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=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="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"></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></BLOCKQUOTE></DIV></DIV></BLOCKQUOTE></BODY></HTML>