Update Query

[Jenmomigs]

I have an update query that I would like to run to update a field with sequential numbers (i.e. 1, 2, 3, 4, etc)
The table that I want to update has a large amount of records, which is why I just want to run an update query. How do I build the "update to:" so that it just autopopulates the field with numbers?
Thanks!

 

[Melagan]

I'm not sure how to do this with SQL, but in case your need is urgent, I thought I would post a VB idea.

In a new module, paste the following, and change "tblMyTable" to match your table name, and rst![Record_Sequence] to match the field name you're tracking the sequence in.

Sub mySequence()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim i As Integer
    i = 0
    strSQL = "SELECT * FROM tblMyTable"
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)
    
    If rst.BOF And rst.EOF Then
        Exit Sub
    End If
        rst.MoveFirst
    Do While Not rst.EOF
        i = i + 1
        rst.Edit
        rst![Record_Sequence] = i
        rst.Update
        rst.MoveNext
    Loop
    
End Sub

This aside, I'm sure there is a JetSQL solution that will do this, I just don't know it =)

~Melagan
______
"It's never too late to become what you might have been.

 

[Melagan]

Oh, I forgot to mention - you can run this code by creating a new form with a command button, and behind the buttons OnClick event:

Private Sub Command0_Click()
Call mySequence
End Sub

OR, if you are familiar with debugging tools, go back to the module where you pasted (and saved) the code from my first reply and then STEP INTO (F8 on your keyboard, or find under the Debug menu) the sub, then hit the play button do have it run through the code without breaks. You decide which is easier, or wait for an SQL solution =)

~Melagan
______
"It's never too late to become what you might have been.