By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,148 Members | 759 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,148 IT Pros & Developers. It's quick & easy.

Update Query which counts the occurrence of duplicate records

P: n/a
How can I query an existing table and update a field in each record in the
table with the occurrence count of each record e.g. update field to 1 (=
first record occurrence), update field to 2 for 2nd record occurrence i.e.
first duplicate record), update the field to 3 for the 3rd record occurrence
i.e. 2nd duplicate record

Example for a duplicated record (field with value ABCD)
Rec 1 ABCD 1
Rec 2 ABCD 2 (first duplicate)
Rec 3 ABCD 3 (2nd duplicate)
Rec 4 ABCD 4 (3rd duplicate)

Any help appreciated.
Thanks,
Bob
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Bob,

Here's the test table, entered the text, the cnt fields were blank before I
ran the code below. Not sure why the later records got the earlier numbers.
If you want the first record to get cnt=1, maybe there's an id field you can
add to the sort to make this happen. You'll need a reference to Microsoft
DAO 3.6 Object Library.
textfld cnt
ABCD 4
ABCD 3
AB 2
ACD 3
ACD 2
AB 1
ABCD 2
DEF 1
EF 2
ACD 1
EF 1
ABCD 1
Public Function AddCnt() As Boolean
Dim num As Integer
Dim savetext As String
Dim firstrec As Boolean
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("select textfld, cnt from tblTextCnt order
by textfld")

firstrec = True
If Not rst.EOF Then
rst.MoveFirst
While Not rst.EOF
rst.Edit
If firstrec Then
num = 1
savetext = rst.Fields("textfld")
firstrec = False
Else
If rst.Fields("textfld") = savetext Then
num = num + 1
Else
savetext = rst.Fields("textfld")
num = 1
End If
End If
rst.Fields("cnt") = num
rst.Update
rst.MoveNext
Wend
End If

rst.Close
End Function

HTH Linda
"Robert" <ro*******@bigpond.com> wrote in message
news:d7******************@news-server.bigpond.net.au...
How can I query an existing table and update a field in each record in the
table with the occurrence count of each record e.g. update field to 1 (=
first record occurrence), update field to 2 for 2nd record occurrence i.e.
first duplicate record), update the field to 3 for the 3rd record occurrence i.e. 2nd duplicate record

Example for a duplicated record (field with value ABCD)
Rec 1 ABCD 1
Rec 2 ABCD 2 (first duplicate)
Rec 3 ABCD 3 (2nd duplicate)
Rec 4 ABCD 4 (3rd duplicate)

Any help appreciated.
Thanks,
Bob

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.