[AccessD] Delete query problem

Thanks to everyone for there assistance. Got it working. Turns out it was deleting the correct number of records. Datasheet view was showing duplicates.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Monday, August 10, 2009 8:38 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Delete query problem

This should do it:

DELETE *
FROM [tbl Inactive Injectors Any Day Prior Month]
WHERE PID in
(Select PID from [tbl Statuses During Prior Month]
   WHERE Status In
   ("FL","FM","FH","SL","SM","SH","SP","PL","PM","PH","RP","RM","RH","RL",
   "TA","WD","WC","WF","WCH","WAGW","WAGC")
);

-- 
Stuart


On 10 Aug 2009 at 8:02, Kaup, Chester wrote:

> Tried your suggestion and get a message of cannot delete from specified tables. Tables are local and not open.
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
> Sent: Thursday, August 06, 2009 4:36 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Delete query problem
> 
> Looks like you built a select statement and them just changed the Select to Delete.
> 
> Try removing the  "[DISTINCTROW]" and the 
> ", [tbl Statuses During Prior Month].Status"
> 
> -- 
> Stuart
> 
> On 6 Aug 2009 at 16:25, Kaup, Chester wrote:
> 
> DELETE DISTINCTROW  [tbl Inactive Injectors Any Day Prior Month].*, [tbl Statuses During Prior Month].Status
> FROM [tbl Inactive Injectors Any Day Prior Month] 
> INNER JOIN [tbl Statuses During Prior Month] ON [tbl Inactive Injectors Any Day Prior Month].PID = [tbl Statuses During Prior Month].PID
> WHERE ((([tbl Statuses During Prior Month].Status) 
> In ("FL","FM","FH","SL","SM","SH","SP","PL","PM","PH","RP","RM","RH","RL","TA","WD","WC","WF","WCH","WAGW","WAGC")));
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com