[AccessD] Generating a rownumber field in a query

Bruce,

    So long as the source table has a primary key field, simplified function Fn_RowNum() as given below, gets sequential row numbers, starting from 1. Its use in a sample query is demonstrated below.

    This function has an additional advantage that the output is stable as compared to alternatives using increments to static or global variables, where the results are volatile, prone to change with repeat navigation (up and down) through the column concerned.

    Q_Source is the source query (where contents of source table / tables are duly collected and sorted as desired), while ID is the name of primary key field in source table. If raw data in a single source table is desired to be used directly, without any sorting, name of this table can be used (in lieu of Q_Source), in the sample query given below.

Best wishes,
A.D.Tejpal
--------------

Sample Query (for generating sequential row numbers)
Based upon source query Q_Source.
(ID is the name of primary key field).
=====================================
SELECT Q_Source.*, 
Fn_RowNum("Q_Source","ID",[ID]) AS RowNum 
FROM Q_Source;
=====================================

Fn_RowNum() - User defined function
=====================================
Function Fn_RowNum(ByVal QueryName As String, _
                 ByVal PrimaryKeyName As String, _
                 ByVal PrimaryKeyValue As Long) As Long
' Returns Row number for the record having primary key field
' named PrimaryKeyName with a value = PrimaryKeyValue,
' in source query named QueryName
    Dim Rct As Long
    Dim rst As DAO.Recordset
    
    Rct = 0
    Set rst = CurrentDb.OpenRecordset(QueryName)
    rst.FindFirst PrimaryKeyName & " = " & PrimaryKeyValue
    If Not rst.NoMatch Then
        Rct = rst.AbsolutePosition + 1
    End If
    
    Fn_RowNum = Rct
    rst.Close
    Set rst = Nothing
End Function
=====================================

  ----- Original Message ----- 
  From: Bruen, Bruce 
  To: AccessD at databaseadvisors.com 
  Sent: Friday, June 10, 2005 07:38
  Subject: [AccessD] Generating a rownumber field in a query


  Hi folks,
   
  I want to output a sequence number as a field in a query result.
  Everytime the query runs it should restart the sequence number from 1.
   
  The query appends rows to a "testscript" table, every new test cycle we generate a new set of test scripts which is made up of all the tests in the previous cycle plus new tests that were added during the execution of the last cycle.  The natural key for the output table is the cycle number + the sequence number.
   
  Cycle    Seq    Testname .....   
     1          1        blah
     1          2        blah blah
  ...
     2          1        blah blah
     2          2        blah blah blah
  etc
   
  The cycle number is input as a parameter to the append query.  But how can I generate the sequence numbers?
   
  tia
  bruce