Delete Results from Append Query

[schweer]

Thanks for your input, Scott. I'll post up my code; see if you can identify any errors in it.

DELETE [MasterFileEdit].[OID], [MasterFileEdit].[SystemLoc], [MasterFileEdit].[SystemPN], [MasterFileEdit].[Location], [MasterFileEdit].[Part_Number], [MasterFileEdit].[Qty], [MasterFileEdit].[Date_n_Time], [MasterFileEdit].[NewLoc], [MasterFileEdit].[RecheckLocation], [MasterFileEdit].[RecheckPart], [MasterFileEdit].[oddeven], [MasterFileEdit].[Auditor], [MasterFileEdit].[upperlower], [MasterFileEdit].[area], [MasterFileEdit].[aisle], [MasterFileEdit].[section]
FROM MasterFileEdit
WHERE MasterFileEdit.SystemPN IN (Select * From [Location Initial Audit]);

Location Initial Audit is the table that the records are originally appended to. I'd like to take the records in that table and delete them from the Masterfile.

It keeps giving me the following error when I try and run the query:
'You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field.'

Thanks,
Matt

 

[ScottGem]

when you run a Delete query it deletes the WHOLE record. Therefore there is no need to list fields except to provide criteria. Second your subquery needs only one field

DELETE * FROM MasterFileEdit
WHERE MasterFileEdit.SystemPN IN (Select SystemPN From [Location Initial Audit]);

The error message actually explains the problem since Access wouldn't know what field in the subquery to use. eVen if the subquery only has one column, Access doesn't know that at the point that it evaluates the main query.

 

[schweer]

Thanks so much Scott. The query worked flawlessly.

I'm fully prepared to donate my first-born to you. I've been stuck on this damn query for 2 weeks. I can now finish my project.

Thanks again, Scott. I appreciate it.

-Matt

 

[ScottGem]

First borns are not necessary but I do accept paypal Being put on a pedastal is also nice