How Do I make it so that the row number resets whenever a certain field changes in a MS Access qry?

Question

I’m using this code and I’m trying to get it so that the row number resets whenever a certain field changes in a qry. I have the below to functions and then I use it in the qry. I export the query to use elsewhere.

Any Ideas and or samples?

For example, if field A is orange, orange, orange, banana, apple, apple, grapefruit.
Then I’m looking to have this in field B: 001, 002, 003, 001, 001, 002, 001

Option Compare Database

Private lngRowNumber As Long

Public Function RowNumber(UniqueKeyVariant As Variant) As Long
lngRowNumber = lngRowNumber + 1
RowNumber = lngRowNumber
End Function

Public Function ResetRowNumber() As Boolean
lngRowNumber = 0
ResetRowNumber = True
End Function

Query

SELECT 
TBL_Test.RowID, 
TBL_Test.Cust_Number, 
TBL_Test.Loan_Number, 
RowNumber(TBL_Test.RowID) AS RowNum
FROM TBL_Test
WHERE (((ResetRowNumber())<>False))
ORDER BY TBL_Test.Cust_Number, TBL_Test.Loan_Number;

Calculating a group row number can be done without VBA. https://stackoverflow.com/questions/70052620/access-query-counter-per-group — June7, Dec 17 '21 at 04:07
Elaborating on June7: you can count the fruits using VBA, but it turns out to require more code and be less portable than using a correlated subquery. However correlated subqueries cause me pain, and the example has a small error so try pasting the following into the sql pane of the access query designer: SELECT t.FruitName AS FieldA, (select count(*) from Fruits as t2 where (t2.FruitName = t.FruitName and t2.ID <= t.ID)) AS FieldB FROM Fruits AS t; — mazoula, Dec 17 '21 at 07:34

score 1 · Answer 1 · answered Dec 17 '21 at 07:52

First, you miss is a unique ID, then an expanded function that takes a group key.

So, add an AutoNumber field to table as the first step.

Next, at my project VBA.RowNumbers you can find my function RowNumber which has the option for a group key:

' Builds consecutive row numbers in a select, append, or create query
' with the option of a initial automatic reset.
' Optionally, a grouping key can be passed to reset the row count
' for every group key.
'
' Usage (typical select query having an ID with an index):
'   SELECT RowNumber(CStr([ID])) AS RowID, *
'   FROM SomeTable
'   WHERE (RowNumber(CStr([ID])) <> RowNumber("","",True));
'
' Usage (typical select query having an ID without an index):
'   SELECT RowNumber(CStr([ID])) AS RowID, *
'   FROM SomeTable
'   WHERE (RowNumber("","",True)=0);
'
' Usage (with group key):
'   SELECT RowNumber(CStr([ID]), CStr[GroupID])) AS RowID, *
'   FROM SomeTable
'   WHERE (RowNumber(CStr([ID])) <> RowNumber("","",True));
'
' The Where statement resets the counter when the query is run
' and is needed for browsing a select query.
'
' Usage (typical append query, manual reset):
' 1. Reset counter manually:
'   Call RowNumber(vbNullString, True)
' 2. Run query:
'   INSERT INTO TempTable ( [RowID] )
'   SELECT RowNumber(CStr([ID])) AS RowID, *
'   FROM SomeTable;
'
' Usage (typical append query, automatic reset):
'   INSERT INTO TempTable ( [RowID] )
'   SELECT RowNumber(CStr([ID])) AS RowID, *
'   FROM SomeTable
'   WHERE (RowNumber("","",True)=0);
'
' 2020-05-29. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function RowNumber( _
    ByVal Key As String, _
    Optional ByVal GroupKey As String, _
    Optional ByVal Reset As Boolean) _
    As Long
    
    ' Uncommon character string to assemble GroupKey and Key as a compound key.
    Const KeySeparator      As String = "¤§¤"
    ' Expected error codes to accept.
    Const CannotAddKey      As Long = 457
    Const CannotRemoveKey   As Long = 5
  
    Static Keys             As New Collection
    Static GroupKeys        As New Collection

    Dim Count               As Long
    Dim CompoundKey         As String
    
    On Error GoTo Err_RowNumber
    
    If Reset = True Then
        ' Erase the collection of keys and group key counts.
        Set Keys = Nothing
        Set GroupKeys = Nothing
    Else
        ' Create a compound key to uniquely identify GroupKey and its Key.
        ' Note: If GroupKey is not used, only one element will be added.
        CompoundKey = GroupKey & KeySeparator & Key
        Count = Keys(CompoundKey)
        
        If Count = 0 Then
            ' This record has not been enumerated.
            '
            ' Will either fail if the group key is new, leaving Count as zero,
            ' or retrieve the count of already enumerated records with this group key.
            Count = GroupKeys(GroupKey) + 1
            If Count > 0 Then
                ' The group key has been recorded.
                ' Remove it to allow it to be recreated holding the new count.
                GroupKeys.Remove (GroupKey)
            Else
                ' This record is the first having this group key.
                ' Thus, the count is 1.
                Count = 1
            End If
            ' (Re)create the group key item with the value of the count of keys.
            GroupKeys.Add Count, GroupKey
        End If

        ' Add the key and its enumeration.
        ' This will be:
        '   Using no group key: Relative to the full recordset.
        '   Using a group key:  Relative to the group key.
        ' Will fail if the key already has been created.
        Keys.Add Count, CompoundKey
    End If
    
    ' Return the key value as this is the row counter.
    RowNumber = Count
  
Exit_RowNumber:
    Exit Function
    
Err_RowNumber:
    Select Case Err
        Case CannotAddKey
            ' Key is present, thus cannot be added again.
            Resume Next
        Case CannotRemoveKey
            ' GroupKey is not present, thus cannot be removed.
            Resume Next
        Case Else
            ' Some other error. Ignore.
            Resume Exit_RowNumber
    End Select

End Function

Then you can build this query:

SELECT 
    Fruit.[Field A], 
    Format(RowNumber(CStr([Id]),[Field A]),"000") AS [Field B]
FROM 
    Fruit
WHERE 
    RowNumber(CStr([Id]))<>RowNumber("","",True);

which will output:

How Do I make it so that the row number resets whenever a certain field changes in a MS Access qry?

1 Answers1