Add Auto number in Access Query

This Access tutorial explains how to add auto number in Access Query using Access VBA Function.

Add Auto number in Access Table

In Microsoft Access, go to Design View of a Table and define the Data Type of a field as AutoNumber

Access add Auto number 01

 

Go to data view of the Table, each row of data is assigned a sequence number in ascending order.

Access add Auto number 02

However, we cannot add the AutoNumber Data Type in Query Design View, therefore we need to workaround to add auto number in Access Query.

Add Auto number in Access Query

To add auto number in Access Query, there are several solutions in Google. Some solutions have very low performance for large data set, some are related to assign ranking (not assigning sequence as is). Among all solutions, I prefer the one from tek-tips.

This solution creates a VBA Function, then you can use the Function directly in Query Expression.

Step 1 – Create User Defined Function

Press ALT + F11 > insert a Module > copy and below and paste to the Module.

Global seqNumber As Long
Global lastcall As Date

Function wAutoNumber(i) As Long
    If Now - lastcall > 4 / 60 / 60 / 24 Then
        lastcall = Now
        seqNumber = 0
    End If
    seqNumber= seqNumber+ 1
    wAutoNumber = seqNumber
End Function

This Function adds 1 each time the Function is run. The Function is run from row one data to the last row of the Query, therefore row 1 will have an auto number of 1, row 2 will have 2.

The auto number (seqNumber) is stored as a Global variable, meaning the variable will not be reset to zero even after the Function is ended. Therefore we need to define when the variable will reset. The statement

Now - lastcall > 4 / 60 / 60 / 24

defines 4 seconds as the time for reset, you can change 4 to other number. Note that if you run this Query within 4 seconds, the auto number will continue to increase.

If you try to run a Macro to export more than 1 Queries without waiting for 4 seconds, try the following Sub

Public Sub exp()
    seqNumber= 0
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Query1", "C:\test\Query1.xlsx", True
    seqNumber= 0
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Query2", "C:\test\Query2.xlsx", True
End Sub

Step 2 – Add Expression in Query

Add an Expression in a Query using the above User Defined Function. Actually the parameter is meaningless, it can be any field name, it is added so that the Function can work probably.

Access add Auto number 03

Result

Run the Query, now the auto number is generated in a new field.

Access add Auto number 04

Note that the number begins with 2 instead of 1, I believe it is because Access runs the Function before running the Function for the first record in this Datasheet View.

However, if you export the Query to Excel, you will see the number begins with 1.

Access add Auto number 05

If you want the auto number to start with 1 in Datasheet View (but it will start with 0 if you export the data), simply replace

seqNumber = 0

with

seqNumber= -1

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

2 thoughts on “Add Auto number in Access Query

  1. Hello, I am completely new to the Access databases. I really like your code, but is it possible to make it not relating to the date? I have some problems when running iterative code with this code.

Leave a Reply

Your email address will not be published. Required fields are marked *