count and remove duplicate access vba
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 = t开发者_Go百科blcat1_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.
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.
精彩评论