Assigning "seq_num" in a query

Question

I am attempting to run an update query to add a running "seq_num" to a table based off of "batch_num"  I have the "batch_num", but I need to add the "seq_num".

Here is what I'm attempting to do.  How can I add a counter (seq_num) to my update query?

Batch_Num	Seq_Num
001	001
001	002
002	001
002	002
003	001

Answer 1

As you have no unique key, the only method is to loop the records and update the sequence number as you go. This works:

Public Function FillSequence()

    Dim Records         As DAO.Recordset

    Dim Sql             As String

    Dim LastBatch       As String

    Dim ThisBatch       As String

    Dim LastSequence    As String

    Dim ThisSequence    As String

    Sql = "SELECT * FROM Batch ORDER BY Batch_Num, Abs([Seq_Num] Is Null), Seq_Num;"

    Set Records = CurrentDb.OpenRecordset(Sql)

    While Not Records.EOF

        ThisBatch = Records!Batch_Num.Value

        If LastBatch <> ThisBatch Then

            LastBatch = ThisBatch

            ' Reset sequence.

            LastSequence = "000"

        End If

        If Not IsNull(Records!Seq_Num.Value) Then

            ' Old batch.

            LastSequence = Records!Seq_Num.Value

        Else

            ' New batch.

            ThisSequence = Format(Val(LastSequence) + 1, "000")

            Records.Edit

                Records!Seq_Num.Value = ThisSequence

            Records.Update

            LastSequence = ThisSequence

        End If

        Records.MoveNext

    Wend

    Records.Close

End Function

Answer 2

You might like to take a look at RowNumbering.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This little demo file includes three examples of queries for sequentially numbering grouped rows.  Two examples, using a JOIN and a subquery respectively, return a non-updatable result table, the third, using the DCount function returns an updatable result table, but is less efficient.   For an UPDATE query you'd need to use the last method of course.

Answer 3

With enough detail about the actual table itself, someone could try to offer suggestions. 

Among those details would be the name of the table, which field(s) it contains, and so on.

That said, I question the wisdom of storing a calculated "Seq_Num" to the table in the first place. 

Thanks for filling out the picture so we can have enough information to work with.