Add Auto number in Access Query

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

You may also want to read:

Sort Data in Access Report

Add sorting in Access Table field

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

 

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

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.

Result

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

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.

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