Combine Subsequent Table Records

  • Thread starter Thread starter David Frazell
  • Start date Start date
D

David Frazell

How can I combine two subsequent records from Table1 into a single record in
Table2?

Table1 has two fields, Unique Record ID and Text String.

For example:
ID Text
1 Text string record 1
2 Text string record 2
3 Text string record 3
4 Text string record 4
....
n Text string record n
n+1 Text string record n+1

The result in Table 2 needs to be:
1 Text string record 1 Text string record 2
2 Text string record 3 Text string record 4
3 Text string record 5 Text string record 6
....

Thanks,
Dave F.
 
David,

Try something like this:

Sub populate_table()
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim cnt As Long

cnt = 1
Set rs1 = CurrentDb.OpenRecordset("Table1")
Set rs2 = CurrentDb.OpenRecordset("Table2")
rs1.MoveFirst
On Error GoTo sub_exit
Do Until rs1.EOF
rs2.AddNew
rs2.Fields(0) = cnt
rs2.Fields(1) = rs1.Fields(1)
rs1.MoveNext
rs2.Fields(2) = rs1.Fields(1)
rs1.MoveNext
rs2.Update
cnt = cnt + 1
Loop

sub_exit
rs1.Close
Set rs1 = Nothing
rs2.Close
Set rs2 = Nothing

End Sub

You will need to add DAO to your references, if not already there. From the
VB window go Tools > References, and add Microsoft DAO 3.x (3.51 for A97,
3.6 for A2K or later).

HTH,
Nikos
 
Back
Top