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
