Note that if you run this Query within 4 seconds, the auto number will continue to increase. The statement Now - lastcall > 4 / 60 / 60 / 24ĭefines 4 seconds as the time for reset, you can change 4 to other number. Therefore we need to define when the variable will reset. 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. 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. This Function adds 1 each time the Function is run. If Now - lastcall > 4 / 60 / 60 / 24 Then Press ALT + F11 > insert a Module > copy and below and paste to the Module. This solution creates a VBA Function, then you can use the Function directly in Query Expression.
Among all solutions, I prefer the one from tek-tips. Some solutions have very low performance for large data set, some are related to assign ranking (not assigning sequence as is). To add auto number in Access Query, there are several solutions in Google.
However, we cannot add the AutoNumber Data Type in Query Design View, therefore we need to workaround to add auto number in Access Query. Go to data view of the Table, each row of data is assigned a sequence number in ascending order. In Microsoft Access, go to Design View of a Table and define the Data Type of a field as AutoNumber
This Access tutorial explains how to add auto number in Access Query using Access VBA Function.Īdd sorting in Access Table field Add Auto number in Access Table