function for row number and/or conditional grouping

[CJ_London]

Hopefully the notes are self explanatory. This function will return a row number based on a PK or other unique field or a group number based on some grouping value such as an FK. Note the value does not have to be number, it could be a date, text or boolean.

It handles the situation where similar functions might recalculate when the user selects a row. It is also extremely fast when compared with sql equivalents.

Just copy and paste the whole function into a standard module. Note the highlighted WHERE clause is required to initialise the collection. If not included you will get an error (one for each row) the first time you run it and subsequently an incorrect result.

The function can only be used once in any query (including sub queries/referenced queries) otherwise you will get incorrect results. Similarly if you require two group numbers in a single query (e.g. a row number and a grouping based on an FK) you will need a copy of the function with a different name (Perhaps call one rowNumber?)

Queries run consecutively should not be a problem so long as one query finishes before the next one starts.

The query does not need to be sorted but obviously makes sense for grouping.

The returned value can be selected and the column sorted - values are not recalculated unless a record is added or deleted and the recordset requeried. Some may see this as an advantage, others a disadvantage but it is what it is.

Function GroupNumber(Optional v As Variant = -1) As Long
'Provides a group number to provide a value for conditionally formatting groups of rows or just as a row number
'For conditional formatting use something like 'expression is....groupnumber mod 2' for alternate groups, mod 3 for triple groups etc
'use pk or other unique field for the v parameter for row number otherwise whatever value forms a basis for the grouping - such as week number or FK
'use in query like this
'SELECT *, GroupNumber(datepart('ww',mydate)) as GroupWeek
'FROM myTable
'WHERE GroupNumber() = True
'Order By myDate 

'***** WARNING *****
'after the query has been executed, the colGroup collection will remain in memory. It is advisable to call GroupNumber (in VBA) without a parameter to free up the memory afte the query is run e.g.
'...
'...
'rs=currentdb.openrecordset("SELECT....
'GroupNumber
'...
'...

Static L As Long 'group number accumulator
Static colGroup As Collection 'of v's where a number has already been assigned
Dim t As Long 'temp value

    If v = -1 Then 'initialise static values (called by the where clause)
    
        Set colGroup = New Collection
        L = 0
        GroupNumber = -1 'out of range and -1 returns true
    
    Else

        On Error GoTo addToCollection
        t = colGroup(CStr(v)) 'if in collection use the existing group value - this prevents groupnumbers being recalculated when a row is selected by the user
        
        GroupNumber = t
        
    End If
    
    Exit Function
    
addToCollection:
    
        Select Case Err
            Case 91
                
                MsgBox "colGroup not initialised - include 'GroupNumber()=true' in your query WHERE clause", vbCritical + vbOKOnly, "Error in query call"
                t = 0
                
            Case Else
            
                L = L + 1
                colGroup.Add L, CStr(v) 'to ensure type consistency
                t = L
                
        End Select

        Resume Next
    
End Function

[isladogs]

Hi CJL

Thanks for posting this code

I did a quick check to compare the speed of the GroupNumber approach with the use of Serialize to obtain a rank order. Both were quick but in my limited testing Serialize was usually slightly faster e.g.

qryY7ScAss1MarkGN - time = 0.1875s
qryY7ScAss1MarkRANK - time = 0.1015625s

and again...
qryY7ScAss1MarkGN - time = 0.1328125s
qryY7ScAss1MarkRANK - time = 0.1015625s

but not always...
qryY7ScAss1MarkGN - time = 0.1171875s
qryY7ScAss1MarkRANK - time = 0.125s

As you rightly point out it is a good idea to clear GroupNumber from memory.
I got some peculiar results when I didn't do so

Also, despite your very clear warnings, I forgot to add GroupNumber()=True in my first attempt.
This resulted in the repeated errors you mentioned & I had to use the 3 fingered salute to escape.
It would be good if this error could be handled to avoid this issue but I had no luck with doing this

Haven't yet looked at the other uses of your code you mentioned but it certainly merits further consideration.
I'd be interested in other members' experience with using this code

[CJ_London]

It would be good if this error could be handled to avoid this error but I had no luck with doing this

problem is the function is called for every row. You could just disable the msgbox - but then the developer won't necessarily know there is an error. And there is nothing like being constantly reminded to ensure you don't forget in the future

I'll take a look at your serialise function.

With regards group number I can see plenty of situations where could be useful - lists of invoices/lines, customers/invoices, products/transactions, products/versions, transactions by period to name but a few.

[CJ_London]

Had a look at the Serialise function- they are two different animals. Both are way better compared with using a subquery to provide a hierarchy.

But there are three differences I can see:

1. Serialise is based on a values within the data, GroupNumber is not. So the first row will always be 1, sort the data into a different order and the first row is still 1.

2. for the same reason, filter out some records and Serialise does not recalculate new values whilst with GroupNumber the row numbers are recalculated.

3. Where there are multiple values the same (e.g. FK or scores in the Serialise example), both Serialise and GroupNumber return the same value for each row - however for the next row after the group, serialise takes into account the number of rows in that group, whilst serialise returns +1 e.g.

Serialise....GroupNumber
1...............1
2...............2
2...............2
2...............2
5...............3
6...............4

1 & 2 might produce the same results if Serialise could be based on a form recordset or clone - not tested and probably not what is required given what is identified in 3. For GroupNumber, you have a consistency of increment which is easier to code for highlighting groups.

On performance, it may well be that a dictionary will be faster that a collection - I'll create a dictionary alternative for comparison.

In conclusion, I believe these each serve different requirements as identified in point 3.

[arnelgp]

look at that I already posted similar code to help an OP in sept-2:

Public Function fncList(ByVal p_id As Long, ByVal p_max_rows As Integer) As Integer
    '
    ' arnelgp
    ' 02-sep-2019
    '
    ' p_id      autonumber value or -99.
    '           if -99 is passed, it will reset the collection object.
    ' purpose:
    '
    ' group records in Query
    '
    ' syntax: "select table1.*, fncList([ID]) As Grouping From table1;"
    '
    Const NO_ERROR      As Integer = 0
    Static col_values   As VBA.Collection
    Static int_counter  As Integer
    Static m_max_row    As Integer
    Dim int_ret         As Integer
    If (p_id = -99) Then
        Set col_values = New VBA.Collection
        int_counter = 0
        Exit Function
    End If
    If (col_values Is Nothing) Or (p_max_row <> m_max_row) Then
        Set col_values = New VBA.Collection
        m_max_row = p_max_row
        int_counter = 0
    End If
    On Error Resume Next
    int_ret = col_values(p_id & "")
    If err.Number <> NO_ERROR Then
        err.Clear
        On Error GoTo 0
        int_counter = int_counter + 1
        col_values.Add (int_counter \ (p_max_rows + 1)) + 1, p_id & ""
        int_ret = (int_counter \ (p_max_rows + 1)) + 1
    End If
    fncList = int_ret
End Function

Public Function fncResetList()
Call fncList(-99)
End Function

[isladogs]

Agree that GroupNumber & Serialize (US spelling!) provide slightly different functionality but a couple of extra points about Serialize

1. Serialize is based on the field specified. For example:

PupilRank: Serialize("qryY7scAss1Marks","PupilID",[PupilID])

If you filter the data you would need to alter the Serialize calculation accordingly by basing it on a saved filtered query rather than the original table/query
2. It is also possible to sort by one field e.g. Mark & rank by another e.g. PupilID
This results in unique rank values for each record even where the marks are equal
3. I'd never thought of using the rank order for conditional formatting but that also works using Serialize. Whether that's beneficial or a distraction may depend on the data and the formatting condition used.

See attached screenshots and example app

I'm certainly not saying one method is better than the other - both are useful.
I've not tried arnel's code

[CJ_London]

@arnel - as you say similar.

You have an additional parameter in your function, but it doesn't appear to be populated when the function is called? How does that work? Doesn't it need to be Optional?

[CJ_London]

@Colin - I was think more like this attachment - not very pretty but you get the idea

[isladogs]

That's certainly better than my second (deliberately bad) example with the orange conditional formatting
Out of interest what did you use as your CF rule to get that effect?

[CJ_London]

per my original post

expression is ... [rank] mod 2 =0

where rank is the groupnumber field, and predicated on score

[isladogs]

Thanks. I still had GroupNumber predicated on PupilID instead of Mark!