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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
<My task is to concatenate all the message fields for a given date.>
Even if the  "MGKEY" is different?
ergenbgrAuthor Commented:
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Jeffrey CoachmanMIS LiasonCommented:
I don't understand...
Please refer to your exact Field names...
Helen FeddemaCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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".

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.