Edit column of data to fixed series of sequential numbers.

[aford]

Hi all, here's my problem. I have a temp table that gets filled with 121 records. Each record has a time field (sngTime), but it when it gets imported into our database this column does not always start at 1 and isn't always sequential. I want to use this data in a graph and use the time field as the "X" axis value.

Is there some way i can do some code to edit the values in that column to be sequential starting at 0. This table will always have 121 records.

I've attached a copy of the temp table with an example of how we sometimes get this data.

Any help, pointers would be greatly appreciated.

TIA

 

[KingSassa]

Here is one solution.
Version of Access and database type is assumed.

Sub Sequence()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim RS As DAO.Recordset

Dim N As Integer

Set db = CurrentDb

N = 0

Set RS = db.CreateQueryDef("", "SELECT * FROM ttbl5GAS ORDER BY sngTime").OpenRecordset

RS.MoveFirst

With RS
Do While Not .EOF
.Edit
.Fields!sngTime = N
N = N + 1
.Update
.MoveNext
Loop
End With

End Sub

 

[aford]

KingSassa thanks for the quick reply. I don't have time to play with this today but will give it a try first thing tomorrow morning and let you know how it goes.
Access version is 2003, and we are ADO for the most part but do reference DAO.

 

[KingSassa]

Here is one routine that would work with ADO:

Sub SequenceADO()
Dim rs As New ADODB.Recordset
Dim N As Integer

N = 0


rs.Open "SELECT [sngTime] FROM ttbl5GAS ORDER BY [sngTime]", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

rs.MoveFirst

With rs
Do While Not .EOF
.Fields!sngTime = N
.Update
N = N + 1
.MoveNext
Loop

End With


End Sub

 

[aford]

Finally had a chance to try these out, they both worked. Ended up going with the ADO version.

Thank you for the code. I haven't been doing this type of stuff long and with NO training, so most of the time i either have to look at the existing code and try to edit it to make it do what i want or i start searching.

This was greatly appreciated.