Delete data in 1 Table using Query with 2 tables

[netrusher]

I keep getting the message "Specify the table containing the records you want to delete." whne I try to run my a delete query. I am trying to delete the data from 1 table when it doesn't appear in the other table plus meets some other criteria. Probably something simple I am looking over. I am somehwat of a beginner in Access. Below is the code:

 DELETE [Manager Approval Changes].[Part Number], [Manager Approval Changes].Description, [Manager Approval Changes].[PRMS Total Labor], [Manager Approval Changes].[SOE Total Labor], [Manager Approval Changes].[Total Labor Variance], [Manager Approval Changes].OP, [Manager Approval Changes].Work, [Manager Approval Changes].Center, [Manager Approval Changes].[Current Routing], [Manager Approval Changes].[SOE Time], [Manager Approval Changes].[Mfg Eng Approved], [Manager Approval Changes].[ME Manager Approved], [Manager Approval Changes].[Product Class]
FROM [Manager Approval Changes] LEFT JOIN FBPndRtg ON ([Manager Approval Changes].[Part Number] = FBPndRtg.[Product Number]) AND ([Manager Approval Changes].[SOE Time] = FBPndRtg.[Current SOE Time by W/C])
WHERE ((([Manager Approval Changes].[Part Number]) Is Not Null) AND (([Manager Approval Changes].[Mfg Eng Approved])=True) AND (([Manager Approval Changes].[ME Manager Approved])=True) AND ((FBPndRtg.[Product Number]) Is Null));

 

[PHV]

Perhaps this ?

DELETE [Manager Approval Changes].*
FROM [Manager Approval Changes]
LEFT JOIN FBPndRtg ON [Manager Approval Changes].[Part Number] = FBPndRtg.[Product Number] AND [Manager Approval Changes].[SOE Time] = FBPndRtg.[Current SOE Time by W/C]
WHERE [Manager Approval Changes].[Part Number] Is Not Null
 AND [Manager Approval Changes].[Mfg Eng Approved] = True
 AND [Manager Approval Changes].[ME Manager Approved] = True
 AND FBPndRtg.[Product Number] Is Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dinger2121]

with a delete query, you don't specify the fields to delete. The format is just delete from table where.....
You can't just delete specific fields, you have to delete whole records.

 

[netrusher]

Closer than I was. I am now getting the message "Could not delete from specified tables." I have other delete queries on this table that do work, so I think there is still something lacking in the code??

 

[dinger2121]

I'm not sure if you can use outer joins in a delete query - you may have to look into using sub queries.

 

[PHV]

And this ?

DELETE *
FROM [Manager Approval Changes] M
WHERE M.[Part Number] Is Not Null
 AND M.[Mfg Eng Approved] = True
 AND M.[ME Manager Approved] = True
 AND Not Exists (SELECT * FROM FBPndRtg F WHERE M.[Part Number]=F.[Product Number] AND M.[SOE Time]=F.[Current SOE Time by W/C])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886