auto numbers?

[kato68]

I have a table with over 12,000 rows. In this table there is a date field. The dates range from 1973 - 1999. I want to add another field, almost like an autonumer field, but I want the numbers to start over for ever date. So 1973 will have 1+, 1974 will have 1+, etc... Is there any easy way to do this???

I thought about extracting each of the years into different tables, setting an autonumber on each table, and then appending them all together, but that seems too involved.

Any ideas?


Ultimately I am going to create unique IDs for every record by having the date with the unique number on the end. 19730001, 19740001, 19740002. That is why I am trying to do this.

 

[Nouba]

sorry, misinterpreted the question.

 

[WayneRyan]

Kato,

Here's a code way to do this, I don't have Access, so I'm not
sure about the format syntax.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String
Dim ThisYear As String
Dim SeqNumber As Long

Set dbs = CurrentDb
sql = "Select * from YourTable Order by TheDate"
Set rst = dbs.OpenRecordset(sql)

LastYear = ""

While Not rst.EOF and Not rst.BOF
    If CStr(DatePart("yyyy", rst!TheDate)) <> ThisYear Then
       ThisYear = CStr(DatePart("yyyy", rst!TheDate))
       SeqNumber = 1
    End If
    rst.Edit
    rst!NewSequenceField = ThisYear & Format(SeqNumber, "0000")
    rst.Update    
    SeqNumber = SeqNumber + 1
    rst.MoveNext
    Wend

Wayne