Concatenating fields from multiple records

I have an Access table that contains records of customer notes. The notes were recorded on an AS400 green screen and the USER was presented with a series of 4 lines in which to record notes or comments. Each line represented a field with with a maximum of 50 characters. Therefore if the USER needed to make a comment containing more than 50 characters, he or she would drop to the next line and continue typing, however each line is stored as a separate record.  The attached file is a subset of the 13,000 records in the database.

My task is to concatenate all the message fields for a given date. Each date being a new record in new data table that I will create using the advice I receive here. On the attached spreadsheet I have chosen on example (yellow shading rows 28-35). Row 65 (shaded in pinkish) is the result I want. An I need to do it for 13,000 records comprised of maybe 2400 separate entities (accounts).

Any and all help will be appreciated and rewarded with a speedy award of points.
Who is Participating?
Helen FeddemaConnect With a Mentor Commented:
This will do the job (I made a table from your workbook, and added a Notes field of Memo type):

Public Sub ConcatenateData()
'Created by Helen Feddema 27-Aug-2013
'Last modified by Helen Feddema 27-Aug-2013

On Error GoTo ErrorHandler

   Dim rst As DAO.Recordset
   Dim strMGKey As String
   Dim strPreviousMGKey As String
   Dim strMGDate As String
   Dim strPreviousMGDate As String
   Dim strNotes As String
   Set rst = CurrentDb.OpenRecordset("tblTestData", dbOpenDynaset)
   'Get starter values
   strPreviousMGKey = rst![MGKEY]
   strPreviousMGDate = rst![MGDATE]
   strNotes = ""
   Do While Not rst.EOF
      strMGKey = rst![MGKEY]
      strMGDate = rst![MGDATE]
      'Debug.Print "New Key: " & strMGKey
      'Debug.Print "New Date: " & strMGDate
      'Debug.Print "Previous Key: " & strPreviousMGKey
      'Debug.Print "Previous Date: " & strPreviousMGDate
      If strMGKey = strPreviousMGKey And strMGDate = strPreviousMGDate Then
         strNotes = strNotes & " " & Trim(rst![MGMSG])
         'Debug.Print "Notes: " & strNotes
         'On different Key or different date for same key
         rst![Notes] = strNotes
         strNotes = ""
      End If
      strPreviousMGKey = strMGKey
      strPreviousMGDate = strMGDate
   Exit Sub

   MsgBox "Error No: " & Err.Number _
      & " in ConcatenateData procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

Jeffrey CoachmanMIS LiasonCommented:
<My task is to concatenate all the message fields for a given date.>
Even if the  "MGKEY" is different?
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

ergenbgrAuthor Commented:
Jeffrey CoachmanMIS LiasonCommented:
I don't understand...
Please refer to your exact Field names...
Helen FeddemaCommented:
ergenbgrAuthor Commented:
Thank you very much. I got pulled to a network problem yesterday and di not get a chance to respond but your solution is perfect. Maybe someday I can repay.
ergenbgrAuthor Commented:
No comment
ergenbgrAuthor Commented:
I am having a small issue with the code you so graciously wrote for me and not sure how to edit it to correct the issue. It appears to miss the record that contains the first line of the note.

I have uploaded a revised copy of your database. I revised some data in the table only. I also added a .rtf file to show the expected results from the code.

Sorry to bother you, but I am so close yet I can't "ring the bell".

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.