Concatenating data from more than one record of the same field

[RayRayC]

I need some ACCESS VB code to go thru one table that has multiple records for any given key and a 'Notes' field and then create another table with one record for the same record key, as the first table, that has a new 'Notes' field that has the notes for that key concatenated.

[MSeda]

I haven't tested the following code but it should be close to what you need. See what you can do with it and post back.

Dim mySQl As string
Dim KeyRS as new adodb.recordset
Dim NoteRs as new adodb.recordset
Dim myNote as string
 
mySQL = “SELECT DISTINCT anygivenKEY FROM agTbl;”
 
KeyRS.Open mySQL
 
Do Until KeyRS.EOF
 
mySQL = “SELECT Notes FROM journalTBL WHERE [NoteKEY] = “ & keyRS.Fields(“anygivenKEY”) & “AND Not ISNull([Notes]);” 
 
NoteRS.open mySQL
 
    Do Until NoteRS.EOF
    If isnull(myNote) then
    myNote = noteRS.Fields(“Notes”)
    Else
    MyNote = MyNote & “, “ & noteRS.Fields(“Notes”)
    Endif
    NoteRS.movenext
    Loop
 
NoteRS.Close
 
mySQL = “INSERT INTO noteTBL (noteKEY, concNote) VALUES (“ & keyRS.Fields(“anygivenKEY”) & “, ‘” & myNote & “’);”
docmd.runSQl mySQL
 
KeyRS.movenext
Loop
 
KeyRS.Close