I've searched for this many times and I am very grateful to all the posts on variants of VBA script that call an incrementing function to get row_numbering into a select query.
The goal is add a row-counter for data that can be grouped e.g.
(not part of query) |
Name |
Car |
Car_ID |
1 |
Andrea |
Mustang |
1 |
2 |
Andrea |
Shelby Cobra |
2 |
3 |
Betty |
Jeep |
1 |
4 |
Betty |
Rav4 |
2 |
5 |
Claire |
BMW |
1 |
This is the code that I used which still has some of the problems in other scripts (namely in datasheet view the first row constantly increments and it can only be used once at a time).
In a new module
Code:
Option Compare Database
Option Explicit
Private ReferenceRowID As Long
Private ReferenceGroupID As Variant
Private lngRowNumber As Long
Public Function Row_Number(varChangeField As Variant) As Long
If varChangeField = ReferenceGroupID Then 'the group by field is the same as previous, increment the row counter
lngRowNumber = lngRowNumber + 1
Else
ReferenceGroupID = varChangeField 'the group by field has changed, reset the counter to 1
lngRowNumber = 1
End If
Row_Number = lngRowNumber
End Function
The Design View picture is attached
The SQL is:
SELECT tbl_Cars.fldName, tbl_Cars.fldCar, Row_Number([fldName]) AS CarID FROM tbl_Cars GROUP BY tbl_Cars.fldName, tbl_Cars.fldCar ORDER BY tbl_Cars.fldName, tbl_Cars.fldCar;
Hope this helps.