Query to delete duplicates is not working 1

[vba317]

Hello,
I am trying to develop a query that will delete a duplicate record and keep the original record. I have developed a select query that works. It does identify the duplicated records properly. But when I change it to a delete query the totals row goes away and I can't figure out how to set the criteria to Max(ID), I have tried Max([ID]) in the criteria section but I get the error Cannot have aggregate function in Where clause(tmp_MthStatsBaaPos.ID = Max(ID) and tmp_MthStatsBaaPos.BillMonthEnc in. Any help is appreciated.

Tom

DELETE tmp_MthStatsBaaPos.ID, tmp_MthStatsBaaPos.BillMonthEnc, tmp_MthStatsBaaPos.POS, tmp_MthStatsBaaPos.[SumOfCountOfCase #], tmp_MthStatsBaaPos.SumOfSumOfUnits, tmp_MthStatsBaaPos.[SumOfCountOfOR Flag], tmp_MthStatsBaaPos.[SumOfSumOfOR Units], tmp_MthStatsBaaPos.SumOfSumOfAmt, tmp_MthStatsBaaPos.SumOfSumOfTotpay, tmp_MthStatsBaaPos.SumOfSumOfTotadj, tmp_MthStatsBaaPos.SumOfSumOfCurBal, tmp_MthStatsBaaPos.ARDays, tmp_MthStatsBaaPos.Last3MonChgs
FROM tmp_MthStatsBaaPos
WHERE (((tmp_MthStatsBaaPos.ID)=Max([ID])) AND ((tmp_MthStatsBaaPos.BillMonthEnc) In (SELECT [BillMonthEnc] FROM [tmp_MthStatsBaaPos] As Tmp GROUP BY [BillMonthEnc],[POS],[SumOfCountOfCase #],[SumOfSumOfUnits],[SumOfCountOfOR Flag],[SumOfSumOfOR Units],[SumOfSumOfAmt],[SumOfSumOfTotpay],[SumOfSumOfTotadj],[SumOfSumOfCurBal] HAVING Count(*)>1  And [POS] = [tmp_MthStatsBaaPos].[POS] And [SumOfCountOfCase #] = [tmp_MthStatsBaaPos].[SumOfCountOfCase #] And [SumOfSumOfUnits] = [tmp_MthStatsBaaPos].[SumOfSumOfUnits] And [SumOfCountOfOR Flag] = [tmp_MthStatsBaaPos].[SumOfCountOfOR Flag] And [SumOfSumOfOR Units] = [tmp_MthStatsBaaPos].[SumOfSumOfOR Units] And [SumOfSumOfAmt] = [tmp_MthStatsBaaPos].[SumOfSumOfAmt] And [SumOfSumOfTotpay] = [tmp_MthStatsBaaPos].[SumOfSumOfTotpay] And [SumOfSumOfTotadj] = [tmp_MthStatsBaaPos].[SumOfSumOfTotadj] And [SumOfSumOfCurBal] = [tmp_MthStatsBaaPos].[SumOfSumOfCurBal])));

 

[Andrzejek]

Is the field tmp_MthStatsBaaPos.ID the PK for this table?
If so, can you get the Select statement to just get the IDs that you want to Delete?
If so, you can do:

Delete from tmp_MthStatsBaaPos
Where ID IN (your Select to get the IDs to delete)

Have fun.

---- Andy

 

[PHV]

What about this ?
DELETE * FROM tmp_MthStatsBaaPos A WHERE ID=(
SELECT Max(ID) FROM tmp_MthStatsBaaPos
WHERE BillMonthEnc=A.BillMonthEnc AND POS=A.POS AND [SumOfCountOfCase #]=A.[SumOfCountOfCase #]
AND SumOfSumOfUnits=A.SumOfSumOfUnits AND [SumOfCountOfOR Flag]=A.[SumOfCountOfOR Flag]
AND [SumOfSumOfOR Units]=A.[SumOfSumOfOR Units] AND SumOfSumOfAmt=A.SumOfSumOfAmt
AND SumOfSumOfTotpay=A.SumOfSumOfTotpay AND SumOfSumOfTotadj=A.SumOfSumOfTotadj
AND SumOfSumOfCurBal=A.SumOfSumOfCurBal
GROUP BY BillMonthEnc HAVING Count(*)>1)

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

 

[vba317]

Yes, the PK is the ID field. When I tried to put the select query inbetween the () I get a syntax error.

DELETE FROM tmp_MthStatsBaaPos
WHERE ID IN(SELECT Max(tmp_MthStatsBaaPos.ID) AS MaxOfID, tmp_MthStatsBaaPos.BillMonthEnc, tmp_MthStatsBaaPos.POS, tmp_MthStatsBaaPos.[SumOfCountOfCase #], tmp_MthStatsBaaPos.SumOfSumOfUnits, tmp_MthStatsBaaPos.[SumOfCountOfOR Flag], tmp_MthStatsBaaPos.[SumOfSumOfOR Units], tmp_MthStatsBaaPos.SumOfSumOfAmt, tmp_MthStatsBaaPos.SumOfSumOfTotpay, tmp_MthStatsBaaPos.SumOfSumOfTotadj, tmp_MthStatsBaaPos.SumOfSumOfCurBal, tmp_MthStatsBaaPos.ARDays, tmp_MthStatsBaaPos.Last3MonChgs)
GROUP BY tmp_MthStatsBaaPos.BillMonthEnc, tmp_MthStatsBaaPos.POS, tmp_MthStatsBaaPos.[SumOfCountOfCase #], tmp_MthStatsBaaPos.SumOfSumOfUnits, tmp_MthStatsBaaPos.[SumOfCountOfOR Flag], tmp_MthStatsBaaPos.[SumOfSumOfOR Units], tmp_MthStatsBaaPos.SumOfSumOfAmt, tmp_MthStatsBaaPos.SumOfSumOfTotpay, tmp_MthStatsBaaPos.SumOfSumOfTotadj, tmp_MthStatsBaaPos.SumOfSumOfCurBal, tmp_MthStatsBaaPos.ARDays, tmp_MthStatsBaaPos.Last3MonChgs
HAVING (((tmp_MthStatsBaaPos.BillMonthEnc) In (SELECT [BillMonthEnc] FROM [tmp_MthStatsBaaPos] As Tmp GROUP BY [BillMonthEnc],[POS],[SumOfCountOfCase #],[SumOfSumOfUnits],[SumOfCountOfOR Flag],[SumOfSumOfOR Units],[SumOfSumOfAmt],[SumOfSumOfTotpay],[SumOfSumOfTotadj],[SumOfSumOfCurBal] 
HAVING Count(*)>1  And [POS] = [tmp_MthStatsBaaPos].[POS] And [SumOfCountOfCase #] = [tmp_MthStatsBaaPos].[SumOfCountOfCase #] And [SumOfSumOfUnits] = [tmp_MthStatsBaaPos].[SumOfSumOfUnits] And [SumOfCountOfOR Flag] = [tmp_MthStatsBaaPos].[SumOfCountOfOR Flag] And [SumOfSumOfOR Units] = [tmp_MthStatsBaaPos].[SumOfSumOfOR Units] And [SumOfSumOfAmt] = [tmp_MthStatsBaaPos].[SumOfSumOfAmt] And [SumOfSumOfTotpay] = [tmp_MthStatsBaaPos].[SumOfSumOfTotpay] And [SumOfSumOfTotadj] = [tmp_MthStatsBaaPos].[SumOfSumOfTotadj] And [SumOfSumOfCurBal] = [tmp_MthStatsBaaPos].[SumOfSumOfCurBal])))
ORDER BY Max(tmp_MthStatsBaaPos.ID), tmp_MthStatsBaaPos.BillMonthEnc, tmp_MthStatsBaaPos.POS, tmp_MthStatsBaaPos.[SumOfCountOfCase #], tmp_MthStatsBaaPos.SumOfSumOfUnits;

 

[vba317]

PHV,
Once again I must thank you for all your help. Your solution worked like a charm!