How to fill a Column with repeating sequence till full using query or vba

[Exi]

I have a table with ID, and PositionID
ID is Autonumber and PositionID is Number.
ID is linked to another table of Records
There is currently 3625 records in ID
so HOW do I fill PositionID with the Number sequence of 1 to 10 repeating over and over till the last record and save to a temp table using a query or vba, so as More titles are added I can regenerate a new table of PositionID
Hope this makes sense.:banghead:

 

[jdraw]

What exactly are you trying to do and why --in business terms?
What does the table represent? Give tables and fields meaningful names.

 

[The_Doc_Man]

The "syntax" answer is, if you want repeating 1 to 10 perhaps because you are selecting this in another query, you can forget about storing the repeating sequence and instead use

SELECT ID, ..., ( ID MOD 10 ) + 1, ... FROM whatever else goes here... ;

Since this number is computable from another field, our usual advice is to NEVER store it, but just always compute it in a SELECT query and use THAT as your record source.

 

[Uncle Gizmo]

Try:-

Sub fRSL_RepeatingSequence()

Dim curDB As DAO.Database
Dim rsRepeatingSequence As DAO.Recordset

Set curDB = CurrentDb

    Dim strSQL_RSL As String
        strSQL_RSL = "SELECT ID, PositionID FROM tblIDandPositionID ORDER BY ID"
    
    Dim X As Integer
    Dim S As Integer
    S = 45
            
            Set rsRepeatingSequence = curDB.OpenRecordset(strSQL_RSL) ', dbOpenForwardOnly)
               
                    Do Until rsRepeatingSequence.EOF
                    
                    X = X + 1
                    
                    If X = S Then Let X = 1
                    
                        rsRepeatingSequence.Edit
                        rsRepeatingSequence!PositionID = X
                        rsRepeatingSequence.Update

                        rsRepeatingSequence.MoveNext
                    Loop
    
End Sub     'fRSL_RepeatingSequence

 

[Exi]

awesome worked a treat
Thanks a million