How to copy data from some columns and paste in another table

[isha]

There are two access tables. One having(called table1) about 15 columns and 1000 rows(records). There is one other access table(called table2) having about 20 columns. i want to copy the data from about 10 columns of table1 and paste it into the columns of table2(the column names are different). I don't know how to do this. Anyone who can help is welcome.

 

[pgk]

Hi,
Try the following. I have used DAO to access the tables. I have put in a command button ( Called Copy ), clicking on which will copy ten fields from Table1 to Table2.

Private Sub Copy_CLick()

Dim db as DAO.Database
Dim rs1 as DAO.Recordset
Dim rs2 as DAO.Recordset

Set db=CurrentDB
Set rs1=db.OpenRecordset("Table1",dbOpenDynaset)
Set rs2=db.OpenRecordset("Table2",dbOpenDynaset)

rs1.MoveFirst
Do While Not rs1.EOF
rs2.AddNew
rs2.Fields("col_1&quot=rs1.Fields("field_1&quot
rs2.Fields("col_2&quot=rs1.Fields("field_2&quot
rs2.Fields("col_3&quot=rs1.Fields("field_3&quot
rs2.Fields("col_4&quot=rs1.Fields("field_4&quot
' Code for adding the remaining 6 fields

rs2.Update
rs1.MoveNext
Loop

rs1.Close
rs2.Close
db.Close
Set db=Nothing
Set rs1=Nothing
Set rs2=Nothing

End Sub

In the above code, substitute the fields col_1,col_2 etc. and field_1 etc. with the actual fields names in the tables. Hope it helps. Let me know what happens.
With regards,
PGK

 

[RobPotts]

Are the columns just getting added to the bottom of the list. If so you could use an append query

Rob