[AccessD] Delete Query problem

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.
 
Charlotte Foust

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

	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?

	 

	Joyce Robinson

	 

	 

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

	 

	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.

	 

	Charlotte Foust

		-----Original Message-----
		From: Robinson, Joyce
[mailto:JoyceRobinson at oasas.state.ny.us] 
		Sent: Tuesday, June 10, 2003 1:25 PM
		To: accessd at databaseadvisors.com
		Subject: RE: [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/638fbd9e/attachment-0001.html>