Why doesn't this work?

[Guest]

I am trying to delete a record from one table that has a code that is
standard = yes in another table. I've tried it two ways and I get the same
error that reads, "Specify the table containing the records you want to
delete."

Try 1:
DELETE DISTINCTROW Clean_Export.Rec_Ident
FROM Clean_Export LEFT JOIN Cntr_Checks ON
Clean_Export.Rec_Ident=Cntr_Checks.CNTR_CODE
WHERE (((Cntr_Checks.STANDARD)=Yes));

Try 2:
DELETE DISTINCTROW Clean_Export.Rec_Ident, Cntr_Checks.Standard
FROM Clean_Export LEFT JOIN Cntr_Checks ON Clean_Export.Rec_Ident =
Cntr_Checks.Cntr_Code
WHERE (((Cntr_Checks.Standard)=Yes));

This is going to make me crazy!

 

[Guest]

Very unlikely that it's going to work with DISTINCTROW or DISTINCT so that
has to go. Next there is the problem of deleting records where there is a
join.

SELECT Clean_Export.Rec_Ident
FROM Clean_Export
WHERE Clean_Export.Rec_Ident IN
(SELECT Cntr_Checks.CNTR_CODE
FROM Cntr_Checks
WHERE Cntr_Checks.STANDARD=Yes);

See if the above returns the correct record. If so change it to a delete
statement.

 

[Jeff L]

Try this:

DELETE FROM Clean_Export
Where Clean_Export.Rec_Ident IN(Select CNTR_Code From Cntr_Check Where
Standard = Yes)

 

[Michel Walsh]

Hi,


DELETE DISTINCTROW Clean_Export.*
FROM ....



Hoping it may help,
Vanderghast, Access MVP