Deduping in VBA using an ID field???

This is what i have so far:

Code:

---

CntTblSQL = "SELECT sorted.LOC_ID, Count(sorted.LOC_ID) AS CountOfLOC_ID INTO CntTable FROM sorted GROUP BY sorted.LOC_ID ORDER BY sorted.LOC_ID;"

DoCmd.SetWarnings False
DoCmd.RunSQL CntTblSQL
DoCmd.SetWarnings True
   
pass1 = "SELECT * FROM CntTable"
dataconnect.Open pass1, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

With dataconnect
Do While Not dataconnect.EOF

strLocId = dataconnect.Fields("LOC_ID").Value
Count = dataconnect.Fields("CountofLOC_ID").Value

MsgBox strLocId
MsgBox Count
.MoveNext
Loop

End With
dataconnect.Close
End Sub

---

I have no idea which way to go next, basically i have a field called "LOC_ID" that has a bunch of duplicates in it. I need to select one of each variation using the "LOC_ID" field while still keeping all the other fields in the record.
any help would be much appreciated!

Welcome to VBForums :wave:

Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.

When the LOC_ID is duplicated, are all the other fields dupicated too, or do they have different data?

e.g. for (LOC_ID, Otherfield1, Otherfield2)

Is it:-
1 ,Blah, Rhubarb
1, Blah, Rhubarb

Or:-
1, Blah, Rhubarb
1, Blah, Yada-Yada

If they are the same you can GROUP BY (in your SQL) the other fields to lose the dupes.
If they are different you need some logic to define which of the records is the correct one.

They have different data. Basically I need to select one of each different LOC_ID, but the remaining fields need to be be kept as is. I have about 70,000 records and only 101 different variations of LOC_ID, so eventually I will wind up with only 101 records left, each having a different LOC_ID.