none
Create Sequential Index In Table RRS feed

  • Question

  • Hi:

    I want to create a sequential index (0,1,2 etc.) and attach it to each row in a table. I can create an initial value of 0 for all rows of the new variable. But how do I update each one so that the result appears in the correct order. How do I code a counter and cycle it through, say, 5000 records?

    tbl_Submissions_test:

    Index     State     User ID       Market           Effective Date

    0            AK         11082        Individual       01/01/2014

    0            AK         11082        Individual       01/01/2015

    0            AK         38344        Small Group   01/01/2014

    0            AK         38344        Small Group   01/01/2015          etc.

    Wednesday, July 20, 2016 1:45 PM

Answers

  • Firstly, do not use index as a column name as it is a 'reserved' word.

    Establish a recordset ordered by the columns whose value determine the order in which you want the rows to be sequentially numbered and update the value of the Index column at each iteration of a loop through the recordset, e.g.

        Dim rst as DAO.Recordset
        Dim strSQL As String
        Dim n As Integer

        ' drop unique index on SeqIndex column
        ' remark out the next two lines the first time the code is executed if the index does not exist,
        ' unremark the lines after executing code for the first time
        strSQL = "DROP INDEX SeqIndex ON YourTable"
        CurrentDb.Execute strSQL, dbFailOnError

        ' update values in SeqIndex column.
        strSQL = "SELECT SeqIndex FROM YourTable ORDER BY <column list goes here>"
        Set rst = CurrentDb.OpenRecordset(strSQL)

        With rst
            .MoveLast
            .MoveFirst
            Do While Not .EOF
                .Edit
                .Fields("SeqIndex") = n
                .Update
                n = n + 1
                .MoveNext
            Loop
        End With

        ' reinstate index on SeqIndex column
        strSQL = "CREATE UNIQUE INDEX SeqIndex ON YourTable(SeqIndex)"
        CurrentDb.Execute strSQL, dbFailOnError

    The code can be executed repeatedly at any time in the future to update the values in the SeqIndex column in the event of rows having been inserted into or deleted from the table, or if any of the values in the columns which determine the order of the index have been changed.
    Wednesday, July 20, 2016 4:37 PM

All replies

  • Add your field with datatype AutoNumber and be sure its 'New Values' property is set to Increment.

    -Bruce

    Wednesday, July 20, 2016 2:38 PM
  • Firstly, do not use index as a column name as it is a 'reserved' word.

    Establish a recordset ordered by the columns whose value determine the order in which you want the rows to be sequentially numbered and update the value of the Index column at each iteration of a loop through the recordset, e.g.

        Dim rst as DAO.Recordset
        Dim strSQL As String
        Dim n As Integer

        ' drop unique index on SeqIndex column
        ' remark out the next two lines the first time the code is executed if the index does not exist,
        ' unremark the lines after executing code for the first time
        strSQL = "DROP INDEX SeqIndex ON YourTable"
        CurrentDb.Execute strSQL, dbFailOnError

        ' update values in SeqIndex column.
        strSQL = "SELECT SeqIndex FROM YourTable ORDER BY <column list goes here>"
        Set rst = CurrentDb.OpenRecordset(strSQL)

        With rst
            .MoveLast
            .MoveFirst
            Do While Not .EOF
                .Edit
                .Fields("SeqIndex") = n
                .Update
                n = n + 1
                .MoveNext
            Loop
        End With

        ' reinstate index on SeqIndex column
        strSQL = "CREATE UNIQUE INDEX SeqIndex ON YourTable(SeqIndex)"
        CurrentDb.Execute strSQL, dbFailOnError

    The code can be executed repeatedly at any time in the future to update the values in the SeqIndex column in the event of rows having been inserted into or deleted from the table, or if any of the values in the columns which determine the order of the index have been changed.
    Wednesday, July 20, 2016 4:37 PM
  • Mike - This sounds like a maintenance nightmare. Why do you need a column like that? As Ken suggested, putting an index on the required fields would keep the rows "in order" but relational database tables should not have to rely on row ordering. Sorting can be done with forms and reports...the only places users should see records.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Wednesday, July 20, 2016 8:08 PM
  • I assumed the OP wanted to be able to track the order in which the records were created.  AFAIK an autonumber is the only effective way to do that in Access.  If there is already some order imposed by other fields in the table then yes, the additional index is redundant.

    -Bruce

     
    Wednesday, July 20, 2016 9:38 PM
  • A datetime stamp with a default of Now() would work just as well as an AutoNumber unless input is a bulk insert.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, July 26, 2016 3:36 PM