count and remove duplicate access vba

Question

Here is some code I have to remove duplicate occurances of catid & recid in tblcat1_rec table, leaving only 1 entry:

mysql = "DELETE FROM tblcat1_rec "
mysql = mysql & " WHERE ID <> (SELECT Min(ID) AS MinOfID FROM tblcat1_rec AS Dupe "
mysql = mysql & " WHERE (Dupe.catid = tblcat1_rec.catid) "
mysql = mysql & " AND (Dupe.recid = tblcat1_rec.recid)); "
DoCmd.RunSQL mysql

I'd like for a count of the total amount of total duplicates found to be put into the "TL" column of the record that remains. Which would also mean, a value of 1 for records that are allready unique.

Answer 1

I think you will have to break this down into two parts. Firstly put the count of all the records for each catid & recid and then take 1 off. After that you can run the delete SQL statement you have here.