Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-01-2019, 05:04 AM   #1
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,330
Thanks: 40
Thanked 3,669 Times in 3,537 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
function for row number and/or conditional grouping

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.


Code:
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

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button

Last edited by CJ_London; 10-01-2019 at 05:23 AM.
CJ_London is offline   Reply With Quote
Old 10-02-2019, 12:04 PM   #2
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,973
Thanks: 114
Thanked 3,002 Times in 2,729 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: function for row number and/or conditional grouping

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
Attached Files
File Type: zip AssessmentRankQueries_v2.zip (84.1 KB, 24 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld

Last edited by isladogs; 10-02-2019 at 02:21 PM.
isladogs is offline   Reply With Quote
Old 10-02-2019, 02:12 PM   #3
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,330
Thanks: 40
Thanked 3,669 Times in 3,537 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: function for row number and/or conditional grouping

Quote:
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
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 10-02-2019, 03:27 PM   #4
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,330
Thanks: 40
Thanked 3,669 Times in 3,537 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: function for row number and/or conditional grouping

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.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 10-02-2019, 10:58 PM   #5
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: function for row number and/or conditional grouping

look at that I already posted similar code to help an OP in sept-2:
Code:
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
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 10-03-2019, 02:41 AM   #6
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,973
Thanks: 114
Thanked 3,002 Times in 2,729 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: function for row number and/or conditional grouping

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:
Code:
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
Attached Images
File Type: png Rank Order By PupilID & Mark + Formatting.PNG (33.2 KB, 20 views)
File Type: png Rank Order By Mark + Conditional Formatting.PNG (33.9 KB, 21 views)
Attached Files
File Type: zip AssessmentRankQueries_v3.zip (86.7 KB, 20 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
Old 10-03-2019, 02:15 PM   #7
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,330
Thanks: 40
Thanked 3,669 Times in 3,537 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: function for row number and/or conditional grouping

@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
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 10-03-2019, 02:27 PM   #8
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,330
Thanks: 40
Thanked 3,669 Times in 3,537 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: function for row number and/or conditional grouping

@Colin - I was think more like this attachment - not very pretty but you get the idea
Attached Images
File Type: png Capture.PNG (23.7 KB, 24 views)
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 10-03-2019, 02:58 PM   #9
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,973
Thanks: 114
Thanked 3,002 Times in 2,729 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: function for row number and/or conditional grouping

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?
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
Old 10-03-2019, 03:21 PM   #10
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,330
Thanks: 40
Thanked 3,669 Times in 3,537 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: function for row number and/or conditional grouping

per my original post

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

where rank is the groupnumber field, and predicated on score
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 10-03-2019, 03:45 PM   #11
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,973
Thanks: 114
Thanked 3,002 Times in 2,729 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: function for row number and/or conditional grouping

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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Grouping number field into intervals in a query Ksel Queries 5 07-02-2014 09:28 PM
DLookup function in report (using grouping) BCote689 Reports 1 01-09-2009 10:38 AM
report by grouping with substr function sunil3071 Reports 7 05-19-2008 09:00 PM
Grouping Data by Week Number choward Forms 1 03-04-2008 05:19 AM
grouping two tables which have different number of data mhisma Queries 2 09-23-2007 09:07 PM




All times are GMT -8. The time now is 06:40 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World