How to use an incremental sequence in a query (instead of an Autonumber) (3 Viewers)

[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!