By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,921 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

Concatenating data from more than one record of the same field

P: 1
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.
Mar 8 '07 #1
Share this Question
Share on Google+
1 Reply


MSeda
Expert 100+
P: 159
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.

Expand|Select|Wrap|Line Numbers
  1. Dim mySQl As string
  2. Dim KeyRS as new adodb.recordset
  3. Dim NoteRs as new adodb.recordset
  4. Dim myNote as string
  5.  
  6. mySQL = “SELECT DISTINCT anygivenKEY FROM agTbl;”
  7.  
  8. KeyRS.Open mySQL
  9.  
  10. Do Until KeyRS.EOF
  11.  
  12. mySQL = “SELECT Notes FROM journalTBL WHERE [NoteKEY] = “ & keyRS.Fields(“anygivenKEY”) & “AND Not ISNull([Notes]);” 
  13.  
  14. NoteRS.open mySQL
  15.  
  16.     Do Until NoteRS.EOF
  17.     If isnull(myNote) then
  18.     myNote = noteRS.Fields(“Notes”)
  19.     Else
  20.     MyNote = MyNote & “, “ & noteRS.Fields(“Notes”)
  21.     Endif
  22.     NoteRS.movenext
  23.     Loop
  24.  
  25. NoteRS.Close
  26.  
  27. mySQL = “INSERT INTO noteTBL (noteKEY, concNote) VALUES (“ & keyRS.Fields(“anygivenKEY”) & “, ‘” & myNote & “’);”
  28. docmd.runSQl mySQL
  29.  
  30. KeyRS.movenext
  31. Loop
  32.  
  33. KeyRS.Close
  34.  
  35.  
Mar 8 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.