How to update sequential number?

[aspfun]

I have a table like below.
How to add sequential number that will reset to 1 whenever the name changes?

Name Date SeqNum
------------------------------
AAA 1/10/2007
AAA 1/12/2007
AAA 1/15/2007
BBB 2/10/2007
BBB 2/13/2007
CCC 3/1/2007

Name Date SeqNum
------------------------------
AAA 1/10/2007 1
AAA 1/12/2007 2
AAA 1/15/2007 3
BBB 2/10/2007 1
BBB 2/13/2007 2
CCC 3/1/2007 1

 

[omccartney]

Your sequential number is what data type?

 

[aspfun]

number, Integer

 

[pbaldy]

I would not save the value, I'd simply calculate it on the fly. In a query, you can use DCount or a subquery to populate that field. In short, the sequential number for any given record is the count of all records that have the same name and whose date is less than or equal to the given record's date (given your sample data).

 

[ByteMyzer]

The following code should do what you are looking to do (change the highlighted text to the actual names):

Public Sub SequenceTable()

    Dim rs As DAO.Recordset
    Dim lSeq As Long
    Dim sName As String

    Set rs = DBEngine(0)(0).OpenRecordset("SELECT T1.* " _
        & "FROM [b][i]MyTable[/i][/b] T1 " _
        & "ORDER BY T1.[b][i]Name[/i][/b], T1.[b][i]Date[/i][/b]", dbOpenDynaset)
    sName = ""

    Do While Not rs.EOF
        If sName <> rs("[b][i]Name[/i][/b]") Then
            sName = rs("[b][i]Name[/i][/b]")
            lSeq = 1
        End If
        rs.Edit
        rs("[b][i]SeqNum[/i][/b]") = lSeq
        rs.Update
        lSeq = lSeq + 1
        rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing

End Sub

See if this solution works for you.