Insert records from table 2 to tsable 1

[victor64]

Hello,

I have two tables with matching fields LAST:

TABLE 1 (Records)

LAST HOBBY
charles Soccer


TABLE 2 (Records)

LAST INTEREST
charles swimming
charles basketball
charles football

I would like to have the following in TABLE 1 (Records)

LAST HOBBY INTEREST
charles Soccer basketball, football, swimming


Below is my code that inserts the records for matching records, how do I modify it to achieve what I am trying to do?

Thanks.

Code:

Dim mySQL_Statement As String = "UPDATE table1 " & vbNewLine & _
" INNER JOIN " & vbNewLine & _
" table2 " & vbNewLine & _
" ON table1.last " & _
" = table2.last " & vbNewLine & _
" SET table1 interest = " & _
" table2.interest "


Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\aopt2002orgorg.mdb;Persist Security Info=True;Jet OLEDBatabase Password=testaopupdate"
Dim objConnection As New OleDb.OleDbConnection(ConnectionString)

'data adapter
Dim objDataAdapter As New OleDb.OleDbDataAdapter(mySQL_Statement, objConnection)

'dataset object
Dim objDataSet As New DataSet
'fill dataset
objConnection.Open()
objDataAdapter.Fill(objDataSet, "SN")
objConnection.Close()

 

[victor64]

Thanks for your reply.