Increment number when the same value

[Kuba S]

Hi,

I'm trying to do below numbering in access:

apple 1
apple 2
apple 3
apple 4
orange 1
orange 2
plum 1
peach 1
peach 2
peach 3

Does anybody know how i can do that in query?

[TheSmileyCoder]

This code placed in a module can act as a simple cumulative counter:

Public Function cumCount(strText As String) As Long
    'Cumulative counter for query
    Static strOldText As String
    Static lngCounter As Long
    If strText = strOldText Then
        lngCounter = lngCounter + 1
    Else
        lngCounter = 1
        strOldText = strText
    End If
        cumCount = lngCounter
 
End Function

Your SQL would then look like so, for example:

SELECT tbl_Fruit.Text, CumCount([Text]) AS [Counter]
FROM tbl_Fruit
ORDER BY tbl_Fruit.Text;
 

[Kuba S]

That's exactly what i was looking for!!
Thanks very, very much!

[TheSmileyCoder]

Your welcome
TheSmileyCoder