How to re-order sequence number (not id) within a table based on common grouping using SQL/Access?

Table 1 shows an example of data from the table for one Item_ID (there are thousands. total # of records is over 500k). The problem is that the sequence number is often duplicated (e.g., see “01” appears 3 times for the same Item_ID). There is no sequence greater than 12. I need to figure out a way using MS Access/SQL to transform the table so that the sequence is corrected based on the ID (see 2nd table for output), and the numbering is sequential past 12.

I’ve shown the desired output on the sample in Table 2 below.

I can do it in excel using equations, but it is inefficient for the amount of data I have.

Any ideas?

**Table 1**
-------
Item_ID     ItemRoute   Sequence    ID
----------------------------------------------
0-X342-41   00_0KF07L   01      56
0-X342-41   00_0KG05E   01      60
0-X342-41   00_CND      01      70
0-X342-41   00_0KA21G   02      52
0-X342-41   00_0KR35G   02      64
0-X342-41   00_0KA21A   03      51
0-X342-41   00_0KG05F   03      61
0-X342-41   00_0KR05C   04      62
0-X342-41   00_EP       04      71
0-X342-41   00_0KR05F   05      63
0-X342-41   00_41396    05      67
0-X342-41   00_0KG05A   06      57
0-X342-41   00_EP       06      72
0-X342-41   00_44642    07      68
0-X342-41   00_EP       07      73
0-X342-41   00_0KF07A   08      53
0-X342-41   00_41368    08      66
0-X342-41   00_0KG05B   09      58
0-X342-41   00_EP       09      74
0-X342-41   00_44645    10      69
0-X342-41   00_EP       10      75
0-X342-41   00_0KF07B   11      54
0-X342-41   00_41357    11      65
0-X342-41   00_0KF07K   12      55
0-X342-41   00_0KG05C   12      59

**Table 2**
-------
Item_ID     ItemRoute   Sequence    ID
-----------------------------
0-X342-41   00_0KF07L   01      56
0-X342-41   00_0KG05E   13      60
0-X342-41   00_CND      25      70
0-X342-41   00_0KA21G   02      52
0-X342-41   00_0KR35G   14      64
0-X342-41   00_0KA21A   03      51
0-X342-41   00_0KG05F   15      61
0-X342-41   00_0KR05C   04      62
0-X342-41   00_EP       16      71
0-X342-41   00_0KR05F   05      63
0-X342-41   00_41396    17      67
0-X342-41   00_0KG05A   06      57
0-X342-41   00_EP       18      72
0-X342-41   00_44642    07      68
0-X342-41   00_EP       19      73
0-X342-41   00_0KF07A   08      53
0-X342-41   00_41368    20      66
0-X342-41   00_0KG05B   09      58
0-X342-41   00_EP       21      74
0-X342-41   00_44645    10      69
0-X342-41   00_EP       22      75
0-X342-41   00_0KF07B   11      54
0-X342-41   00_41357    23      65
0-X342-41   00_0KF07K   12      55
0-X342-41   00_0KG05C   24      59

Answer

One way of doing this would be to use a pair of nested recordsets and loop through them, keeping counters stored outside. This appears to work on the data shown:

Dim db As DAO.Database
Dim rsID As DAO.Recordset
Dim rsSequence As DAO.Recordset
Dim strSQL As String
Dim intSequence As Integer
Dim intID As Integer
Set db = DBEngine(0)(0)
db.Execute "DELETE * FROM Table2;"
strSQL = "SELECT DISTINCT Sequence FROM Table1 ORDER BY Sequence;"
Set rsSequence = db.OpenRecordset(strSQL)
If Not (rsSequence.BOF And rsSequence.EOF) Then
    intSequence = 0
    Do
        intID = 1
        strSQL = "SELECT DISTINCT ID FROM Table1 WHERE Sequence=" & rsSequence!Sequence & " ORDER BY ID;"
        Set rsID = db.OpenRecordset(strSQL)
        If Not (rsID.BOF And rsID.EOF) Then
            Do
                db.Execute "INSERT INTO Table2 (PK,Item_ID, ItemRoute, Sequence, ID) " _
                    & " SELECT PK,Item_ID, ItemRoute, " & intSequence + intID & ", ID " _
                    & " FROM Table1 " _
                    & " WHERE ID=" & rsID!ID & " AND Sequence=" & rsSequence!Sequence
                intID = intID + 12
                rsID.MoveNext
            Loop Until rsID.EOF
        End If
        intSequence = intSequence + 1
        rsSequence.MoveNext
    Loop Until rsSequence.EOF
End If

Regards,