Deleting a similar record

[KPoe]

Hi,

I have a simple Access Table (LstFax) with "REF" (Client NAme) and "ADDR" (Fax numbers) fields that has been generated by SQL from a custom query form. There are duplicate fax numbers for clients from the same office that I want to delete.

I have a sub to try and delete these similar field based on some code I found on the net using From ... From syntax but am having some problems. I can't find syntax to suport this - maybee its just not available for access

Any help much appreciated. The "REF" field is not important. I would like to keep it in SQL without new tables / queries to save space - keep it seemles for users.

Sub RmvDupRec(TabNam As String, TabFld As String, FldUnq As String)

Dim SQLstr As String

SQLstr = "DELETE FROM " & TabNam
SQLstr = SQLstr & " FROM " & TabNam & " As T1, " & TabNam & " As T2"
SQLstr = SQLstr & " WHERE T1." & TabFld & " = T2." & TabFld
SQLstr = SQLstr & " AND T1." & FldUnq & " > T2." & FldUnq & ";"
MsgBox SQLstr

DoCmd.SetWarnings False
DoCmd.RunSQL SQLstr
DoCmd.SetWarnings True

End Sub

the SQL looks like

DELETE FROM LstFax FROM LstFax As T1, LstFax As T2 WHERE T1.ADDR = T2.ADDR AND T1.REF > T2.REF;

Thanks

KPoe

[kavitadatar]

Try the command as
DELETE FROM LstFax As T1, LstFax As T2 WHERE T1.ADDR = T2.ADDR AND T1.REF > T2.REF;

[KPoe]

thanks kavitadatar ,

No such luck. Error 3128 - specify table containing records you want to delete

I am wondering if I have to go about this a different way?

KPoe

[KPoe]

After a bit more searching, I managed to get the following solution.

First I created another table containing the duplicate fax numbers. The SQL is

SELECT DISTINCTROW LstFax.REF, LstFax.ADDR INTO LstTmp FROM LstFax LEFT JOIN LstFax AS LstFax_1 ON LstFax.ADDR = LstFax_1.ADDR WHERE (((LstFax.ADDR) In (SELECT [ADDR] FROM [LstFax] As Tmp GROUP BY [ADDR] HAVING Count(*)>1 )) AND ((LstFax.REF)>[LstFax_1].[REF]));

I then used this table to delete duplicate records in the origional table.

DELETE DISTINCTROW LstFax.* FROM LstFax RIGHT JOIN LstTmp ON LstFax.REF = LstTmp.REF;

Hope this helps someone

KPoe