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.
TestWorkSheet.xlsx
ergenbgrAsked:
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?
0
ergenbgrAuthor Commented:
KEYS are MGKEY and DATE
0
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...
0
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
   rst.MoveFirst
   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
      Else
         'On different Key or different date for same key
         rst.MovePrevious
         rst.Edit
         rst![Notes] = strNotes
         strNotes = ""
         rst.Update
         rst.MoveNext
      End If
      
      strPreviousMGKey = strMGKey
      strPreviousMGDate = strMGDate
      rst.MoveNext
   Loop
ErrorHandlerExit:
   rst.Close
   Exit Sub

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

End Sub

Open in new window

0

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:
0
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.
0
ergenbgrAuthor Commented:
No comment
0
ergenbgrAuthor Commented:
Helen,
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".

Ray
tblTestDataExpResults.rtf
Concatenate-from-multiple-record.accdb
0
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.