Add data to an empty field in other table

[MarcodeMan]

Hi all,

I have two tables:


1 contains data (2000 unique rows) and a field PersonId that is empty in the beginning.
1 contains setup data with a number of persons with a unique PersonId (let's say 3 records but could be more).
Both tables do not have a relation whatsoever yet (but if neccessary a relationship could be added of course).

How do I update field PersonId in the first table (all 2000 rows), in order with the PersonId from the other table in order?
In the first table I want in the first record PersonId to be filled with 1 (PersonId from the 2nd table), the second record PersonId to be filled with 2, the third record PersonId to be filled with3 and the 4th record PersonId to be filled with PersonId=1 again, and so on until all 2000 rows have PersonId in order by 1-2-3-1-2-3-1-2-3... etc. And obviously, if table contains 4 records, it has to be 1-2-3-4-1-2-3-4-1-2-3... etc.

Who has the clue how to do that?

Thanks, M.

[June7]

Records in database table do not have inherent order. Order is defined by data in sorting criteria. What data would you use to define table 1 record order? Is there an autonumber field that could be relied on? Do you really care what order table 1 records are populated with PersonID?

I expect this will need VBA manipulating recordset objects.

"And obviously, if table contains 4 records, it has to be 1-2-3-4-1-2-3-4-1-2-3... etc"
Do you mean if table 2 has 4 records you want the sequence in table 1 to repeat every 4 records?

Provide sample data either as tables in post or attach file.

Here is one code example:

Dim rs1 As ADODB.Recordset, rs2 As DAO.Recordset
Set rs1 = New ADODB.Recordset
rs1.Open "SELECT PersonID FROM table1", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Set rs2 = CurrentDb.OpenRecordset("SELECT PersonID FROM table2")
Do While Not rs1.EOF
    rs1("PersonID") = rs2("PersonID")
    rs1.Update
    rs1.MoveNext
    rs2.MoveNext
    If rs2.EOF Then rs2.MoveFirst
Loop

[Welshgasman]

Other than running a query 2000/3 times, I have no clue.

[Gicu]

Should be fairly easy to do with a bit of VBA but the question is are you sure the order of table 1 (with 2000 records) is the correct one? If this is a random assignment and the records themselves don't really have any relationships with the persons then you're OK.
I would start by filling an array variable with the PersonIds from Table2; next you open a recordset based on table1 and start editing the PersonId field, incrementing the array index once done to move to the next personid:

Dim rPerson    As DAO.Recordset
Dim v()          As Variant,iCount as integer
Dim rData as DAO.Recordset,i as integer
Set rPerson = CurrentDb.OpenRecordset("Select PersonID From Table2")
rPerson.MoveLast
rPerson.MoveFirst  
v = rPerson.GetRows(rPerson.RecordCount)
iCount=UBound(v) - LBound(v) + 1


Set rData = CurrentDb.OpenRecordset("Select PersonID From Table1")
i=0
Do Until rData.EOF
	if i=icount-1 then i=0    'reset i -assume base 0 array
	rData.Edit
	rData("PersonId")=v(i)
	rData.Update
	i=i+1 'increment the array index
	rData.MoveNext
Loop


rData.close
Set rData=Nothing
Set rPerson=Nothing


msgbox"Done"

Cheers,

[MarcodeMan]

I made a sample database with a description in Word in the Zip-file attached. I feel I am close to the solution, but not there yet. I hope you can help me out here!

[June7]

I edited my previous post to show code that does not utilize a counter variable nor array. Tested with my database and works.

[Gicu]

@June7: I like the code (why the mix of ADO and DAO?), but why edit post #2 and not add it to your post #6? After all, these forums are all for learning\teaching, and the idea is to show the progression, a novice can follow a thread and hopefully get incremental pieces of knowledge from each subsequent post; now your post #2 is totally different then the original, which makes the whole thread irrelevant...

Cheers,

[June7]

I edited the post before I refreshed thread and saw the other posts, hence the followup post to redirect OP.

The ADO recordset opens editable, don't have to set it editable for each record. Could use DAO if preferred. The DAO recordset just needs to be read and it is actually simpler code to open DAO recordset.

I can only get the GetRows method to work with following mods:

Private Sub Knop13_Click()

Dim rPerson As DAO.Recordset
Dim v As Variant, iCount As Integer
Dim rData As DAO.Recordset, i As Integer
Set rPerson = CurrentDb.OpenRecordset("Select Id, SpelerNaam From Spelers")
rPerson.MoveLast
rPerson.MoveFirst
v = rPerson.GetRows(rPerson.RecordCount)
'iCount = UBound(v) - LBound(v) + 1
iCount = rPerson.RecordCount
Set rData = CurrentDb.OpenRecordset("Select SpelerId From List")
i = 0
Do Until rData.EOF
    rData.Edit
    rData("SpelerId") = v(0, i)
    rData.Update
    i = i + 1 'increment the array index
    rData.MoveNext
    If i = iCount Then i = 0 'reset i -assume base 0 array
Loop

rData.Close
Set rData = Nothing
Set rPerson = Nothing

MsgBox "Done"
End Sub

[MarcodeMan]

Thanks, this works perfectly! Another lesson learned... :-) Thanks again!