How can I increment a new field

[Garry]

Hi

Say I have 500 records that has been sorted in ascending order (post code).
How can I increment a new field with 1,3,5 etc for the first 250 records
then the second 250 with 2,4,6 etc

regards, Garry

 

[Garry]

Thanks for your time Mike,

New to Access, where do I place this text, cheers Garry

 

[kingston via AccessMonster.com]

I don't think that this can be done efficiently with queries (at least not
with the given parameters). One way to do this is to make a procedure that
cycles through the recordset and assigns the new field value:

Public Sub AssignNew()

Dim rst1 As New ADODB.Recordset
Dim i As Integer

rst1.Open "SELECT TOP 250 [NewField] FROM [Existing RecordSet];",
CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rst1.MoveFirst
i = 1
While rst1.EOF = False
rst1!NewField = i
rst1.MoveNext
i = i + 2
Wend

rst1.Close

rst1.Open "SELECT [NewField] FROM [Existing RecordSet] WHERE ([NewField]
Is Null);", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rst1.MoveFirst
i = 2
While rst1.EOF = False
rst1!NewField = i
rst1.MoveNext
i = i + 2
Wend

End Sub

[Existing RecordSet] is your already sorted list.

 

[Jamie Collins]

Say I have 500 records that has been sorted in ascending order (post code).
How can I increment a new field with 1,3,5 etc for the first 250 records
then the second 250 with 2,4,6 etc

I don't think that this can be done efficiently with queries (at least not
with the given parameters). One way to do this is to make a procedure that
cycles through the recordset and assigns the new field value:

Public Sub AssignNew()

Dim rst1 As New ADODB.Recordset
Dim i As Integer

rst1.Open "SELECT TOP 250 [NewField] FROM [Existing RecordSet];",
CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rst1.MoveFirst
i = 1
While rst1.EOF = False
rst1!NewField = i
rst1.MoveNext
i = i + 2
Wend

rst1.Close

rst1.Open "SELECT [NewField] FROM [Existing RecordSet] WHERE ([NewField]
Is Null);", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rst1.MoveFirst
i = 2
While rst1.EOF = False
rst1!NewField = i
rst1.MoveNext
i = i + 2
Wend

End Sub

[Existing RecordSet] is your already sorted list.

A few comments.

If the 500 is a variable amount your TOP 250 syntax is going to be a
problem because the '250' must be hard-coded i.e. it cannot be a
parameter value. You can use a standard SQL construct but then your own
comment will apply: "I don't think that this can be done efficiently
with queries".

Speaking of which, you've assumed the OP is doing a one-off population
of a field in a base table. If that were the case, surely it wouldn't
matter how inefficient the query was for 500 rows?

I've assumed this is a temporary 'sequence' number required in the
recordset only, dependent on the number of rows returned at run time.
Something like this:

Dim rs
Set rs = CreateObject("ADODB.Recordset")
With rs
.ActiveConnection = _
"Provider=MSDataShape;" & _
"Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Tempo\New_Jet_DB.mdb"
.Source = _
"SHAPE {" & _
" SELECT ExistingField" & _
" FROM [Existing RecordSet]" & _
" ORDER BY ExistingField" & _
"} APPEND NEW adInteger AS NewField"
.LockType = 4 ' adLockBatchOptimistic

.Open

Dim page2Start As Long
page2Start = CLng(.RecordCount / 2) + 1

Dim i As Long
For i = 1 To .RecordCount
If i < page2Start Then
.Fields("NewField").Value = (i * 2) - 1
Else
.Fields("NewField").Value = (i - page2Start + 1) * 2
End If
.MoveNext
Next

' Do stuff using NewField
.Close
End With

Jamie.

--