Displaying Row numbers column at runtime

Question

I have an employee table with has name, age, city as columns. I want to display a column at run-time for my row numbers starting from 1. I am using SQL in Access.

Answer 1

Call the following function from your query.

Public Function GetNextNum(str As String) As Long
    num = num + 1
    GetNextNum = num
End Function

The caveat is that you must have at least one parameter (even if you don't need one) otherwise the function only gets called once and returns 1 for all the rows.

Before running the query set the global variable num to 0.

Answer 2

You only need one function to obtain a very speedy and even "groupable" row counter with or without automatic reset of the counter.

See in-line comments for typical usage:

Public Function RowCounter( _
  ByVal strKey As String, _
  ByVal booReset As Boolean, _
  Optional ByVal strGroupKey As String) _
  As Long

' Builds consecutive RowIDs in select, append or create query
' with the possibility of automatic reset.
' Optionally a grouping key can be passed to reset the row count
' for every group key.
'
' Usage (typical select query):
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' Usage (with group key):
'   SELECT RowCounter(CStr([ID]),False,CStr[GroupID])) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' The Where statement resets the counter when the query is run
' and is needed for browsing a select query.
'
' Usage (typical append query, manual reset):
' 1. Reset counter manually:
'   Call RowCounter(vbNullString, False)
' 2. Run query:
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable;
'
' Usage (typical append query, automatic reset):
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter("",True)=0);
'
' 2002-04-13. Cactus Data ApS. CPH
' 2002-09-09. Str() sometimes fails. Replaced with CStr().
' 2005-10-21. Str(col.Count + 1) reduced to col.Count + 1.
' 2008-02-27. Optional group parameter added.
' 2010-08-04. Corrected that group key missed first row in group.

  Static col      As New Collection
  Static strGroup As String

  On Error GoTo Err_RowCounter

  If booReset = True Then
    Set col = Nothing
  ElseIf strGroup <> strGroupKey Then
    Set col = Nothing
    strGroup = strGroupKey
    col.Add 1, strKey
  Else
    col.Add col.Count + 1, strKey
  End If

  RowCounter = col(strKey)

Exit_RowCounter:
  Exit Function

Err_RowCounter:
  Select Case Err
    Case 457
      ' Key is present.
      Resume Next
    Case Else
      ' Some other error.
      Resume Exit_RowCounter
  End Select

End Function

Answer 3

You have 5 methods available.

Reports only - Running Sum

If you are using this information for Access reports, there is an easy way that requires no VBA or fancy SQL. Simply add a textbox with control source set =1 then set Running Sum to Over All, done.

The rest of methods listed below applies to forms/datasheets/recordsets

Correlated subquery

You can do a correlated subquery. This solution is totally self-contained but is not very generic. It would be something similar to this:

SELECT
  (
    SELECT COUNT(*)
    FROM Employees AS x
    WHERE x.EmployeeID <= e.EmployeeID
    ORDER BY x.EmployeeID
  ) AS RowNumber,
  e.EmployeeID
FROM Employees AS e;

Note that because of the correlated subqueries, the performance will rapidly decrease as the amount of records increase in the table. You might have to customize the ORDER BY clause to get the desired number assignment if it's not supposed to depend on EmployeeID but something else (e.g. HireDate for instance)

VBA Function to maintain count, forward-only recordset

This method can perform much faster but can be only used once; and certainly not within forms/datasheets because VBA functions are continually evaluated as you navigate around. Thus, this is only appropriate when reading recordset in a forward-only manner. Using a standard VBA module:

Private Counter As Long

Public Function ResetRowNumber() As Boolean
  Counter = 0
  ResetRowNumber = (Counter = 0)
End Function

Public Function GetRowNumber(PrimaryKeyField As Variant) As Long
  Counter = Counter + 1
  GetRowNumber = Counter
End Function

To then use in a query:

SELECT 
  GetRowNumber([EmployeeID]) AS RowNumber,
  EmployeeID
FROM Employees
WHERE ResetRowNumber();

Note the trick of using WHERE to implicitly call the ResetRowNumber function first. Please note this will work only as long there is only one query active; having multiple queries that takes row numbers will cause incorrect results. However the implementation is very simple and much faster.

VBA Function to maintain count and preserve the assignment

This is more expensive than the previous method but still can be cheaper than the correlated subquery solution for a sufficiently large table. This has the advantage of being useful in a form / datasheet because once number are given out, it is given out again. Again, in a standard VBA module:

Private NumberCollection As VBA.Collection

Public Function ResetRowNumber() As Boolean
  NumberCollection = New VBA.Collection
  ResetRowNumber = (NumberCollection.Count = 0)
End Function

Public Function GetRowNumber(PrimaryKeyField As Variant) As Variant
  On Error Resume Next

  Dim Result As Long
  Result = NumberCollection(CStr(PrimaryKeyField))
  If Err.Number Then
    Result = 0
    Err.Clear
  End If

  If Result Then
    GetRowNumber = Result
  Else
    NumberCollection.Add NumberCollection.Count + 1, CStr(PrimaryKeyField)
    GetRowNumber = NumberCollection.Count
  End If
  If Err.Number Then
    GetRowNumber = "#Error " & Err.Description
  End If
End Function

It's important that the input parameter PrimaryKeyValue references a non-nullable column (which a primary key column should be by definition). Otherwise, we'd have no way of knowing which number we should give out if it's already been given out to the record. The SQL is similar as previous method:

SELECT 
  GetRowNumber([EmployeeID]) AS RowNumber,
  EmployeeID
FROM Employees
WHERE ResetRowNumber();

As with previous method, this is only good for one query at a time. If you need multiple queries, then you need twice the layer; a collection to reference which query's collection, then to inspect that query's collection. That might get a bit hairy. You might be also able to get more bang with a Scripting.Dictionary, so that's an alternative looking into.

Note also that the function now returns Variant due to the fact that it may encounter unexpected errors. Because the function can get called several times, potentially hundreds or even thousands of time, we can't pop open a message box, so we can mimic what built-in functions do and return a #Error, which is incompatible with the underlying type of Long we're really using.

Upgrade to SQL Server or other RDBMS

Access is a phenomenal RAD tool for building data-centric application. However, you are not necessarily tied to using its database engine. You could just migrate your data to one of free RDBMS, link using ODBC and continue to use your Access application as before, and get to benefit the power of SQL, including the window function ROW_NUMBER() that makes this much easier to achieve than VBA. If you are looking at doing more than just getting a row number, you might need to consider if you should migrate your data to a different database engine.

For additional references, this may be helpful.