Delete code is giving me the blues. . . (1 Viewer)

[WebFaktor]

The 2 code statements below are from the same query as its represented in both a SELECT and a DELETE query type.

QUESTION / PROBLEM: Can the DELETE code below be modified to effect the same result as the SELECT code? More specifically: If, for example, my table contains one set of duplicates, the SELECT code will present/select only 1 of the duplicate pair. While the DELETE code will present/delete the complete pair???

Thank you in advance,


SELECT DISTINCT TableName.Field1, TableName.Field2
FROM TableName
WHERE (((TableName. Filed1) In (SELECT [Field1] FROM [TableName] As Tmp GROUP BY [Field1],[Field2] HAVING Count ([TableName].[Field1])>1 And [Field2] = [TableName].[Field2])));
______________________________________________

DELETE DISTINCTROW TableName.Field1, TableName.Field2
FROM TableName
WHERE (((TableName. Field1) In (SELECT [Field1] FROM [TableName] As Tmp GROUP BY [Field1],[Field2] HAVING Count ([TableName].[Field1])>1 And [Field2] = [TableName].[ Field2])));

 

[The_Doc_Man]

Write and store the SELECT query as query X.


Then write and store the DELETE query based on query X rather than the actual table.

 

[WebFaktor]

Thanks for Reply The_Doc_Man.

I attempted what you've suggested.

By taking differing approaches I received 2 different results:

1. An error message telling me I could not delete from the specified table.

2. by playing around with the SELECT & DELETE query properties (I.e., Unique value & record settings) I was able to have option to delete all records but not half of duplicate pair as I would like.

Your further guidance is appreciated,