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
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

<My task is to concatenate all the message fields for a given date.>
Even if the  "MGKEY" is different?
Avatar of Ray Ergenbright

ASKER

KEYS are MGKEY and DATE
I don't understand...
Please refer to your exact Field names...
ASKER CERTIFIED SOLUTION
Avatar of Helen Feddema
Helen Feddema
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Ray Ergenbright

ASKER

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.
Avatar of Ray Ergenbright

ASKER

No comment
Avatar of Ray Ergenbright

ASKER

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
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo