Delete query help

[chanman525]

I have a table with Purchase Orders plus some other information. Some of the purchase orders are in the table multiple times because of different item numbers attached. This table is called tblmismatch

I also have a query that pulls out PO's from a different table. After I run this query, I want to delete the PO's in tblmismatch that are = the results from the query. Not sure how to go about this. Can anyone help?

 

[randy700]

[red]BACKUP YOUR DATA FIRST[/red]
Create a new query that joins the table and your mismatch query.

SELECT * FROM tblmismatch
INNER JOIN yourquery ON something = something

Review the results. If this is what you want deleted, just change it to a delete query.

DELETE * FROM tblmismatch
INNER JOIN yourquery ON something = something

Randy

 

[chanman525]

I appreciate the reply. The select query seems to work fine, but when I change to a delete query I get an error "Specify the table containing the records you want to delete".

 

[PHV]

Randy, I'd use this instead:

DELETE [!]tblmismatch.[/!]* FROM tblmismatch
INNER JOIN yourquery ON something = something

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

 

[chanman525]

Forgot to enter my SQL statement

DELETE tblrcvqtymismatch.ID, tblrcvqtymismatch.[Vendor Name], tblrcvqtymismatch.Date, tblrcvqtymismatch.[Order Qty], tblrcvqtymismatch.[Receive Qty], tblrcvqtymismatch.[Item Number], tblrcvqtymismatch.PO, tblrcvqtymismatch.RR, *
FROM tblrcvqtymismatch INNER JOIN qryNegative1s ON tblrcvqtymismatch.PO = qryNegative1s.PO;

 

[randy700]

PHV gave you the corrected DELETE query.
You just need to include which table you are deleting from (as he indicated in [red]red[/red]).

Randy

 

[chanman525]

Got it to work, I had to set the Unique Records to Yes because of another error I received. Thank you guys for your help.