471,337 Members | 995 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,337 software developers and data experts.

Update Query which counts the occurrence of duplicate records

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

Similar topics

4 posts views Thread by Karaoke Prince | last post: by
4 posts views Thread by Rick | last post: by
2 posts views Thread by MVA | last post: by
9 posts views Thread by Dom Boyce | last post: by
1 post views Thread by G Gerard | last post: by
1 post views Thread by dee | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.