Trying to delete duplicates 1

[vba317]

I am trying to write a delete query without much luck. I have three fields of interest in this table. The first field is called AID and the second field is called EncID and the third is called variance. What I am trying to delete if if the number in the AID equals each other, the number in EncID equals each other and the variance is 0 than delete both records. I have written a select query which does select the records I want to delete currently it selects 13 records. When I convert the query to a delete query it deleted 0 records.

Any help is appreciated.

Tom

Select Query that works

SELECT tbl_Errors.AID AS [AID Field], Count(tbl_Errors.AID) AS CountOfAID, tbl_Errors.EncID AS [ENCID Field], Count(tbl_Errors.EncID) AS CountOfEncID, Sum(tbl_Errors.Variance) AS SumOfVariance
FROM tbl_Errors
GROUP BY tbl_Errors.AID, tbl_Errors.EncID
HAVING (((Count(tbl_Errors.AID))>1) AND ((Count(tbl_Errors.EncID))>1) AND ((Sum(tbl_Errors.Variance))=0));

The Delete Query that does not work

DELETE tbl_Errors.AID AS [AID Field], tbl_Errors.AID, tbl_Errors.EncID AS [ENCID Field], tbl_Errors.EncID, tbl_Errors.Variance
FROM tbl_Errors
WHERE (((tbl_Errors.AID)>1) AND ((tbl_Errors.EncID)>1) AND ((tbl_Errors.Variance)=0));

 

[Andrzejek]

Can you get the list of Primary Keys of the records you want to Delete? In some kind of Select statement...

Have fun.

---- Andy

 

[PHV]

Perhaps this ?

DELETE A.*
FROM tbl_Errors A INNER JOIN (
SELECT AID,EncID FROM tbl_Errors GROUP BY AID,EncID HAVING Count(*)>1 AND Sum(Variance)=0
) B ON A.AID=B.AID AND A.EncID=B.EncID

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

 

[vba317]

PHV,
I tried running your query and I get an error Could not delete from specified tables.

 

[vba317]

Some clarifications that may or may not help. tbl_Errors is an access table vs an linked table. It has no primary keys or indexes. The fields in question AID and EncID are Long Integer and Variance is a double.

 

[PHV]

And this ?

DELETE * FROM tbl_Errors
WHERE AID & '_' & EncID IN (
SELECT AID & '_' & EncID FROM tbl_Errors GROUP BY AID,EncID HAVING Count(*)>1 AND Sum(Variance)=0
)

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