assign numbers to the occurence of data

[Guest]

I have a database that contains a field having duplicate data. I have created
another column and I want to insert numbers in the other column that will
start at 1 and increment up to the count of the data. For example if "ABC"
occurs 3 times in the first column then in the second column it will be 1 for
the first occurence of "ABC", 2 for the second occurence of "ABC" and 3 for
the third occurence of "ABC". Similarly, if the data occurs 100 times this
count will increment up to 100 for that data.

Can anyone please let me know how this can be accomplished either
programatically or by a query? Thanks in advance.

 

[tina]

well, assuming that you have multiple occurrences of multiple values, for
example

ABC
ABC
ABC
NBC
CBS
CBS
NBC
ABC
NBC
NBC
CBS
CBS
ABC
CBS
NBC
NBC
NBC
NBC
CBS

a VBA procedure that loops through a recordset will give you this result:

ABC 1
ABC 2
ABC 3
NBC 1
CBS 1
CBS 2
NBC 2
ABC 4
NBC 3
NBC 4
CBS 3
CBS 4
ABC 5
CBS 5
NBC 5
NBC 6
NBC 7
NBC 8
CBS 6

there may be more efficient ways to do this, but it does get the job done:

Public Sub isIdentify()

Dim rst As DAO.Recordset, strVal As String, lngCount As Long

Set rst = CurrentDb.OpenRecordset("MyTable", dbOpenDynaset)

Do
rst.MoveFirst
'find the first record with no value in MyCountField.
Do Until IsNull(rst("MyCountField")) Or rst.EOF
rst.MoveNext
Loop
'if all records have a value in MyCountField, exit the loop.
'otherwise, assign value 1 to MyCountField and record the
'MyDupField value that will be searched for next.
If rst.EOF Then
Exit Do
Else
strVal = Nz(rst("MyDupField"), "")
lngCount = 1
rst.Edit
rst("MyCountField") = lngCount
rst.Update
End If
'find the next record in the set, and assign the incremented count.
Do
rst.FindNext "MyDupField = '" & strVal _
& "' And MyCountField Is Null"
If rst.NoMatch Then
Exit Do
Else
lngCount = lngCount + 1
rst.Edit
rst("MyCountField") = lngCount
rst.Update
End If
Loop
Loop

rst.Close
Set rst = Nothing

' MsgBox "done"

End Sub

replace MyTable, MyDupField, and MyCountfield with the correct table and
field names, of course. you can un-comment the Msgbox when testing the sub,
so you'll know when it's done running. *note* that multiple records with a
zero-length string in MyDupField will be assigned incremented numbers, but
multiple records where MyDupField is Null will each be assigned default
number 1 (not incremented).

hth

 

[Guest]

Thanks Tina, for the quick response. I don't know how long this procedure
will take because I have 25000 records but if "MyDupField" is sorted
ascending, are some changes possible to this code so that it doesn't search
for the same value till it reaches the end and start all over from the
beginning. If there is a way to do this I think it will decrease the time to
complete the procedure. Thanks again.

 

[Ken Snell [MVP]]

Is ther a primary key on the table? Assuming that there is (and that it's
just one field), you can use an update query to write "ranking" values to
the new field that you've added. Something like this (note: use of DCount
will make the query run a bit slowly):

UPDATE Tablename SET NewFieldName =
DCount("*", "Tablename", "DataFieldName='" &
[DataFieldName] & " And PrimaryKeyFieldName<=" &
[PrimaryKeyFieldName]);

 

[Ken Snell [MVP]]

Slight typo corrected below:

UPDATE Tablename SET NewFieldName =
DCount("*", "Tablename", "DataFieldName='" &
[DataFieldName] & "' And PrimaryKeyFieldName<=" &
[PrimaryKeyFieldName]);

--

Ken Snell
<MS ACCESS MVP>

Is ther a primary key on the table? Assuming that there is (and that it's
just one field), you can use an update query to write "ranking" values to
the new field that you've added. Something like this (note: use of DCount
will make the query run a bit slowly):

UPDATE Tablename SET NewFieldName =
DCount("*", "Tablename", "DataFieldName='" &
[DataFieldName] & " And PrimaryKeyFieldName<=" &
[PrimaryKeyFieldName]);

--

Ken Snell
<MS ACCESS MVP>

I have a database that contains a field having duplicate data. I have
created
another column and I want to insert numbers in the other column that will
start at 1 and increment up to the count of the data. For example if
"ABC"
occurs 3 times in the first column then in the second column it will be 1
for
the first occurence of "ABC", 2 for the second occurence of "ABC" and 3
for
the third occurence of "ABC". Similarly, if the data occurs 100 times
this
count will increment up to 100 for that data.

Can anyone please let me know how this can be accomplished either
programatically or by a query? Thanks in advance.

 

[tina]

you're welcome, but on doing further testing i found that my solution was
pretty poor. besides the issue you pointed out, the code kept producing
error 3052:

"File shairng lock count exceeded. Increase MaxLocksPerFile registry entry."

The default number of records that can be updated in a recordset is 9500,
per Help. so while it's possible to update the table by looping through
recordsets, handling the error when it occurs, it's not efficient.

i ended up taking my recordset idea, and incorporating Ken's Update query
solution, and came up with the following, which updates a table with 26,000
records in 0-1 seconds:

'this is a global variable
Public lngCount As Long

Public Function isIncrementedCount(ByVal x As Variant) As Long

'the "x" variable is never used in the function. but you have
'to pull a value from each record in the query, or else
'the function will only be called once - in the first record
'in the query - and all your records will be assigned a value
'of 1.
lngCount = lngCount + 1
isIncrementedCount = lngCount

End Function

Public Sub AssignNumbers()

Dim strSQL1 As String, strSQL2 As String, strVal As String
Dim rst As DAO.Recordset
'the next two variables are only used to time the process.
'you can comment them out anytime - and the code they're used in.
Dim dtBegin As Date, dtEnd As Date

dtBegin = Now
strSQL1 = "SELECT MyDupField FROM MyTable " _
& "WHERE MyDupField Is Not Null " _
& "GROUP BY MyDupField"

Set rst = CurrentDb.OpenRecordset(strSQL1, dbOpenDynaset)
rst.MoveFirst
Do
lngCount = 0
strVal = rst("MyDupField")
'note: you can use any field in the isIncrementedCount function call,
'it doesn't have to be MyDupField.
strSQL2 = "UPDATE MyTable SET MyCountField = " _
& "isIncrementedCount([MyDupField]) " _
& "WHERE MyDupField='" & strVal & "'"
CurrentDb.Execute strSQL2, dbFailOnError
rst.MoveNext
Loop Until rst.EOF

rst.Close
Set rst = Nothing

dtEnd = Now
MsgBox DateDiff("s", dtBegin, dtEnd)

End Sub

again, replace MyTable, MyDupField, and MyCountField everywhere in the code,
with the correct names.

the caveat is that the above code will not process records where MyDupField
is null, or is a zero-length string. if you *may* encounter either scenario
at any time, post back and i'll work up an alternate solution that includes
processing those records too. (shouldn't be hard, i just don't want to
invest the extra time unless needed.)

hth

 

[Guest]

Thanks for all your help

you're welcome, but on doing further testing i found that my solution was
pretty poor. besides the issue you pointed out, the code kept producing
error 3052:

"File shairng lock count exceeded. Increase MaxLocksPerFile registry entry."

The default number of records that can be updated in a recordset is 9500,
per Help. so while it's possible to update the table by looping through
recordsets, handling the error when it occurs, it's not efficient.

i ended up taking my recordset idea, and incorporating Ken's Update query
solution, and came up with the following, which updates a table with 26,000
records in 0-1 seconds:

'this is a global variable
Public lngCount As Long

Public Function isIncrementedCount(ByVal x As Variant) As Long

'the "x" variable is never used in the function. but you have
'to pull a value from each record in the query, or else
'the function will only be called once - in the first record
'in the query - and all your records will be assigned a value
'of 1.
lngCount = lngCount + 1
isIncrementedCount = lngCount

End Function

Public Sub AssignNumbers()

Dim strSQL1 As String, strSQL2 As String, strVal As String
Dim rst As DAO.Recordset
'the next two variables are only used to time the process.
'you can comment them out anytime - and the code they're used in.
Dim dtBegin As Date, dtEnd As Date

dtBegin = Now
strSQL1 = "SELECT MyDupField FROM MyTable " _
& "WHERE MyDupField Is Not Null " _
& "GROUP BY MyDupField"

Set rst = CurrentDb.OpenRecordset(strSQL1, dbOpenDynaset)
rst.MoveFirst
Do
lngCount = 0
strVal = rst("MyDupField")
'note: you can use any field in the isIncrementedCount function call,
'it doesn't have to be MyDupField.
strSQL2 = "UPDATE MyTable SET MyCountField = " _
& "isIncrementedCount([MyDupField]) " _
& "WHERE MyDupField='" & strVal & "'"
CurrentDb.Execute strSQL2, dbFailOnError
rst.MoveNext
Loop Until rst.EOF

rst.Close
Set rst = Nothing

dtEnd = Now
MsgBox DateDiff("s", dtBegin, dtEnd)

End Sub

again, replace MyTable, MyDupField, and MyCountField everywhere in the code,
with the correct names.

the caveat is that the above code will not process records where MyDupField
is null, or is a zero-length string. if you *may* encounter either scenario
at any time, post back and i'll work up an alternate solution that includes
processing those records too. (shouldn't be hard, i just don't want to
invest the extra time unless needed.)

hth

Thanks Tina, for the quick response. I don't know how long this procedure
will take because I have 25000 records but if "MyDupField" is sorted
ascending, are some changes possible to this code so that it doesn't search
for the same value till it reaches the end and start all over from the
beginning. If there is a way to do this I think it will decrease the time to
complete the procedure. Thanks again.

 

[Guest]

Thanks a lot, Ken. It works like a charm. Can you suggest a good book which
has lots of examples on the different SQL queries?

Slight typo corrected below:

UPDATE Tablename SET NewFieldName =
DCount("*", "Tablename", "DataFieldName='" &
[DataFieldName] & "' And PrimaryKeyFieldName<=" &
[PrimaryKeyFieldName]);

--

Ken Snell
<MS ACCESS MVP>

Is ther a primary key on the table? Assuming that there is (and that it's
just one field), you can use an update query to write "ranking" values to
the new field that you've added. Something like this (note: use of DCount
will make the query run a bit slowly):

UPDATE Tablename SET NewFieldName =
DCount("*", "Tablename", "DataFieldName='" &
[DataFieldName] & " And PrimaryKeyFieldName<=" &
[PrimaryKeyFieldName]);

--

Ken Snell
<MS ACCESS MVP>

I have a database that contains a field having duplicate data. I have
created
another column and I want to insert numbers in the other column that will
start at 1 and increment up to the count of the data. For example if
"ABC"
occurs 3 times in the first column then in the second column it will be 1
for
the first occurence of "ABC", 2 for the second occurence of "ABC" and 3
for
the third occurence of "ABC". Similarly, if the data occurs 100 times
this
count will increment up to 100 for that data.

Can anyone please let me know how this can be accomplished either
programatically or by a query? Thanks in advance.

 

[Ken Snell [MVP]]

Here's the one I started with:

SQL Queries for Mere Mortals by Hernandez and Viescas.

--

Ken Snell
<MS ACCESS MVP>

Thanks a lot, Ken. It works like a charm. Can you suggest a good book
which
has lots of examples on the different SQL queries?

Slight typo corrected below:

UPDATE Tablename SET NewFieldName =
DCount("*", "Tablename", "DataFieldName='" &
[DataFieldName] & "' And PrimaryKeyFieldName<=" &
[PrimaryKeyFieldName]);

--

Ken Snell
<MS ACCESS MVP>

Is ther a primary key on the table? Assuming that there is (and that
it's
just one field), you can use an update query to write "ranking" values
to
the new field that you've added. Something like this (note: use of
DCount
will make the query run a bit slowly):

UPDATE Tablename SET NewFieldName =
DCount("*", "Tablename", "DataFieldName='" &
[DataFieldName] & " And PrimaryKeyFieldName<=" &
[PrimaryKeyFieldName]);

--

Ken Snell
<MS ACCESS MVP>



I have a database that contains a field having duplicate data. I have
created
another column and I want to insert numbers in the other column that
will
start at 1 and increment up to the count of the data. For example if
"ABC"
occurs 3 times in the first column then in the second column it will
be 1
for
the first occurence of "ABC", 2 for the second occurence of "ABC" and
3
for
the third occurence of "ABC". Similarly, if the data occurs 100 times
this
count will increment up to 100 for that data.

Can anyone please let me know how this can be accomplished either
programatically or by a query? Thanks in advance.