remove duplicate + count (1 Viewer)

[mikeo1313]

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 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.

 

[jal]

There's probably no way to do this without a temp table because the Jet engine doesn't allow you to run aggregate commands (such as Count or Group By) in an UPDATE query. Also, if you have over, say, ten thousand records, the temp table approach will give better performance when deleting dups.

So here we go. Call the temp table IDs. It will have 2 columns ID (long number) and DupCount (long number). You MUST mark the IDs.ID column as primary key. It's simply going to store the ID#s of the records you wish to keep.


Insert the records you wish to keep

INSERT INTO IDs (ID, DupCount)
SELECT Min(T.ID) as ID, Count (*) as DupCount
FROM tblCat1_rec AS T
GROUP BY T.CatID, T.recID

Now update the TL column

UPDATE tblCat1_rec as T
INNER JOIN IDs
ON IDs.ID = T.ID
SET TL = DupCount


Now here's the performance boost: You can use a left join to do the deletion (runs faster than your delete-code posted above).



DELETE T.* FROM tblCat1_rec as T
LEFT JOIN IDs
ON IDs.ID = T.ID
WHERE IDS.ID IS NULL

 

[mikeo1313]

can anyone spot whats wrong with this line of code


mysql = "UPDATE table1 SET TLs=DCount(*,""shipper='"" & shipper & ""' and filename='"" & filename & ""'"")"


this is supposed to count duplicate occurances of field shipper & filename in table1 table.

I get
run-time error '3075':

Syntax error (missing operator) in query expression
'DCount(*,"shipper='"&shipper & " and filename='"& filename & "".

* I really can't see the ' or * so well but I copied what I thought the exact code was in the message box to the best of my ability.

 

[mikeo1313]

Jal, please excuse, I didn't even see your response. I'll be giving your great suggestion a try now.