Avatar of Ray Ergenbright
Ray ErgenbrightFlag for United States of America asked on

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
Microsoft AccessOffice ProductivityDatabases

Avatar of undefined
Last Comment
Ray Ergenbright

8/22/2022 - Mon
Jeffrey Coachman

<My task is to concatenate all the message fields for a given date.>
Even if the  "MGKEY" is different?
Jeffrey Coachman

ASKER
Ray Ergenbright

KEYS are MGKEY and DATE
Your help has saved me hundreds of hours of internet surfing.
fblack61
Jeffrey Coachman

I don't understand...
Please refer to your exact Field names...
ASKER CERTIFIED SOLUTION
Helen Feddema

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Helen Feddema

ASKER
Ray Ergenbright

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Ray Ergenbright

No comment
ASKER
Ray Ergenbright

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