How to use an incremental sequence in a query (instead of an Autonumber) (1 Viewer)

[TuurCarlsen]

4 steps:

1/ declare a global

Global glolngIncrementSequence As Long


2/ create a function

Public Function fncIncrementSequence(ByVal varDummy As Variant) As Long
' Very important: varDummy must be any non empty text value
glolngIncrementSequence = glolngIncrementSequence + 1
fncIncrementSequence = glolngIncrementSequence
End Function


3/ initiate the global

glolngIncrementSequence = 0


4/ run this query just using CategoryName as the Dummy (adding "x" is to avoid an empty value)

SELECT CategoryName, fncIncrementSequence(CategoryName & "x") AS Seq
FROM Categories
ORDER BY CategoryName


Guess how long it will take to create a table with 100.000 sequenced records ?

SELECT TOP 100000 fncIncrementSequence(a.ID & a.ID * Rnd) AS Seq
INTO tblTEST
FROM MSysObjects AS a, MSysObjects AS b, MSysObjects AS c, MSysObjects AS d, MSysObjects AS e

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[The_Doc_Man]

@TuurCarlsen - My question is whether you realize that your "FROM" syntax is a Cartesian Join.

Also, since your function is written in VBA, not true-compiled but pseudo-compiled, this code might take longer to run than if you defined an auto-number - which is NOT dependent on VBA emulation.

Finally, according to the rules defining the order of SQL execution, you would implement the Cartesian JOIN fairly early, but the TOP predicate (which is related to the LIMIT clause for SQL versions that allow it) is near the end of SQL execution. I.e. you build that five-way Cartesian product which on a reasonable DB might involve 50 to 100 objects, leading to 10,000,000,000 (ten billion entries) and THEN trim it down to 100,000. The amount of space that would take might be a strain on some systems.

EDITED by TDM: In fact, generating anything over 2 billion entries would crash Access since you can only create one table via an INSERT INTO query. I am not going to assume malicious intent here, but what you posted is perhaps harmful in that it would frustrate novice Access users. For that reason alone, I will recommend not attempting the experiment with five copies of MSysObjects in the list because it also occurs to me that you would get a factorial math progression with that many records for a typical Access DB. Large factorials are not pleasant to manipulate.

 

[MarkK]

My 2c about sequential numbering is that it is a presentation concern, not a data concern. Except in the context of items 24 and 26, the fact an item is in row 25 is meaningless. A sequence number is a property of a list. A single data point (a row) carries no concept of order.

And this is reflected in performance as follows...
• Because you must call back to a function for each row, and maintain a counter external to the query itself, you crush a query's performance if you coerce it to render a row-level sequence number.
• Because a presentation mechanism like a report, listview, treeview, etc... is by definition rendered sequentially, incrementing a row-level sequence number has almost zero performance impact.

Thus, coercing a query to render a row-level sequence number incurs a cost with no compensatory payoff.

 

[arnelgp]

you don't save the Sequence in the table.
you generate it on fly, using a Report or a Form.
For form you can use Stephen Lebans code:

' Credit to Stephen Lebans for the function
'
Public Function RowNum(frm As Form) As Variant
On Error GoTo Err_RowNum
    'Purpose:   Numbering the rows on a form.
    'Usage:     Text box with ControlSource of:  =RowNum([Forms]![myForm])
 
    If frm.RecordSource <> "" Then
        With frm.RecordsetClone
            .Bookmark = frm.Bookmark
            RowNum = .AbsolutePosition + 1
        End With
    End If
 
Exit_RowNum:
    Exit Function
 
Err_RowNum:
    If Err.Number <> 3021& Then  'Ignore "No bookmark" at new row.
        Debug.Print "RowNum() error " & Err.Number & " - " & Err.Description
    End If
    RowNum = Null
    Resume Exit_RowNum
End Function

 

[MarkK]

you don't save the Sequence in the table

...or generate it in a query...

 

[TuurCarlsen]

I'm very aware of the risks of carthesian queries.
But the proof of the pudding is in the eating.

Spoiler alert: creating the table with 100.000 records takes ... 1,1 seconds
I just tested the same code but with 1 million records: 9,5 seconds.
In both cases my database grows from 13188 kB to 13192 kB.
But of course, this is just benchmarking.

These are the results on my top 4 tables:
54788: 569 ms
21922: 284 ms
13593: 191 ms
4500: 134 ms

I'm using this blistering fast and very effective method 146 times in my coding for many years.

This is alternative method, which I want to avoid:
- creating a table with an autonumber
- filling the table
- dropping the table afterwards

Just try it.

 

[cheekybuddha]

You can do it with a pure query, without a VBA function - see this post (and thread).