[AccessD] Delete Query problem

Yes, I did that for all queries except the last one (#4) where I did not
directly link the two tables together.

 

-----Original Message-----
From: Charlotte Foust [mailto:cfoust at infostatsystems.com] 
Sent: Tuesday, June 10, 2003 4:54 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Delete Query problem

 

Did you remember to set the query's UniqueRecords property to Yes?

 

Charlotte Foust

	-----Original Message-----
	From: Robinson, Joyce [mailto:JoyceRobinson at oasas.state.ny.us] 
	Sent: Tuesday, June 10, 2003 12:26 PM
	To: AccessD at databaseadvisors.com
	Subject: [AccessD] Delete Query problem

	Hi, 
	  
	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.

	
	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.

	
	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.

	
	Attempt #1 (using From "Clients" right join Q_DropOuts ): 
	DELETE DISTINCTROW Clients.*, Clients.[CID#] 
	FROM Clients RIGHT JOIN [Q_DropOuts] ON Clients.[CID#] =
[Q_DropOuts].[CID#_Link] 
	WHERE (((Clients.[CID#]) Is Not Null)); 
	  
	Attempt #2 (using left join from DropOut query and a non-linking
field in Client table as "not null" criteria): 
	DELETE DISTINCTROW Clients.*, Clients.ID 
	FROM [Q_DropOuts] LEFT JOIN Clients ON [Q_DropOuts].[CID#_Link]
= Clients.[CID#] 
	WHERE (((Clients.ID) Is Not Null)); 
	  
	Attempt #3a and 3b (using inner joins): 
	DELETE DISTINCTROW Clients.* 
	FROM [Q_DropOuts] INNER JOIN Clients ON [Q_DropOuts].[CID#_Link]
= Clients.[CID#]; 
	  
	DELETE DISTINCTROW Clients.* 
	FROM Clients INNER JOIN [Q_DropOuts] ON Clients.[CID#] =
[Q_DropOuts].[CID#_Link]; 
	  
	  
	Attempt #4 (using tables and eliminating query as datasource;
reformatted linking field directly in query) 
	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]

	FROM Clients, [DroppedOut Clients_OL] 
	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#]));

	
	  
	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).  

	
	Thanks very much for any help! 

	 

	NOTE:   Sorry if this is a duplicate message.  I tried to cancel
previous oversized version. 

	
	======Access2002, WinXP Profl 
	  

	 

	~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
	Joyce Robinson [Research Scientist] 
	NYS OASAS  -  Evaluation and Program Monitoring 
	Albany, NY  
	~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
	  
	  
	  

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030610/64b26648/attachment-0001.html>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030610/64b26648/attachment-0001.htm>