[AccessD] Select every nth record

    Criteria for selecting every nth record could be applied to sequential numbers generated by any of the following alternative methods:

    (a) Subquery.
    (b) User defined function.
    (c) Surrogate autonumber.

    For large data sets both (a) & (b) are found to be unacceptably slow. The third method using surrogate autonumber happens to be the fastest. 

    Sample subroutine given below, should be able to append every nth record from source table T_A to destination table T_C (same structure as T_A). ID is the name of primary key in tables T_A, T_B & T_C.

    Table T_B is an interim table. It has two fields named ID (primary key) and RowNum (autonumber type). Care is to be taken that if the primary key (ID) in tables T_A & T_C is autonumber type, it is changed to number type in table T_B.

    On running the subroutine, existing records if any, in tables T_B & T_C get cleared and required data gets filled in a per the argument  NthRecord.

A.D.Tejpal
---------------

==================================
Private Sub P_FillTable(ByVal  _
                                    NthRecord As String)
    Dim db As DAO.Database
    Dim Qst As String
    
    Set db = DBEngine(0)(0)
    
    ' Clear interim table T_B and destination
    ' table T_C
    db.Execute "Delete * From T_B;"
    db.Execute "Delete * From T_C;"
    
    ' Reset RowNum (Autonumber) field in interim
    ' table T_B to start from 1.
    db.Execute "ALTER TABLE T_B  ALTER " & _
                    "COLUMN RowNum COUNTER (1, 1);"

    ' Append ID values from source table T_A to
    ' interim table T_B
    Qst = "INSERT INTO T_B (ID) " & _
            "SELECT ID FROM T_A;"
    db.Execute Qst, dbFailOnError
    
    ' Append every NthRecord to destination table T_C
    Qst = "INSERT INTO T_C " & _
            "SELECT T_A.* FROM T_A " & _
            "INNER JOIN T_B ON T_A.ID = T_B.ID " & _
            "WHERE RowNum Mod " & NthRecord & _
            " = 0;"
    db.Execute Qst, dbFailOnError
    
    Set db = Nothing
End Sub
==================================

  ----- Original Message ----- 
  From: jwcolby 
  To: 'Access Developers discussion and problem solving' 
  Sent: Friday, May 18, 2007 20:36
  Subject: [AccessD] Select every nth record


  Is there a SQL statement that will do this directly?  I have a table of 40K records and I want to split it into two tables, each containing every other record of the 40K record table.

  John W. Colby
  Colby Consulting
  www.ColbyConsulting.com