We help IT Professionals succeed at work.

Roll-up Comments in Access query (or report) on each unique field

319 Views
Last Modified: 2012-05-06
I am trying to create an Access report that will "concatenate" the comments for each unique [WUpID] in my Query.  The problem is, I can't use the CONCATENATE function because (as I understand it) you can only return 255 characters total - there will be times when my Comments will exceed 255 characters. I need the end result to look like the attached picture - with each comment stacked on top of each other in the same field.

Below is my existing Query.  Is there a way that I can have it roll-up my [Comments] from the tbl_WeeklyUpdateDetail for each unique [WUpdID] field?  Should I be doing this on the report side rather than in a query?
SELECT tbl_WeeklyCategory.CategoryName, tbl_WeeklyCategory.WCatID, tbl_WeeklyUpdate.WeeklyUpdName, tbl_WeeklyUpdateDetail.Comment, tbl_WeeklyUpdateDetail.FinalizedDate, tbl_WeeklyUpdateDetail.WUpdID, tbl_WeeklyUpdateDetail.Order
FROM (tbl_WeeklyUpdate INNER JOIN tbl_WeeklyCategory ON tbl_WeeklyUpdate.WCatID = tbl_WeeklyCategory.WCatID) INNER JOIN tbl_WeeklyUpdateDetail ON tbl_WeeklyUpdate.WUpdID = tbl_WeeklyUpdateDetail.WUpdID
WHERE (((tbl_WeeklyUpdateDetail.Status)="Final"))
ORDER BY tbl_WeeklyCategory.WCatID, tbl_WeeklyUpdate.WeeklyUpdName, tbl_WeeklyUpdateDetail.WUpdID, tbl_WeeklyUpdateDetail.Order;

Open in new window

Rollup-Comments.jpg
Comment
Watch Question

I'm had the same problem long time ago, so I do not know exactly if this limit always exists. If so - I used the following workaround (but this is a big effort):

1) I have one query doing the grouping
2) I run this query from another. This query calls a vba-function, doing the concat for each grouped row and saves the result to an static array in vba. The function needs the group (for increasing the array) and your comment-field.
3) I write the array with the group and the comment into a temp. table with a memo field.


Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
I usually do this in a report because I can use VBA to control everything.

Here is a sample.

I can customize it to a certain extent if you post a sample of your data.

JeffCoachman
Access--EEQ23945948SubReportDeta.mdb

Author

Commented:
Jeff,
Thanks so much for the example.  Unfortunately, I played around and didn't get very far since I'm not a developer/coder.  If you have time, would you be able to point me in the right direction - using the attached copy of some of my database?
My form asks the users to enter date parameters...or the Project name (one or the other - can't do both).  From there, you'll see my test report - it should display results like that except that the comments should stack on top of each other (each on a new line).
Any assistance you have time to provide is most appreciated.  Thank you, Stacy
Copy-of-PDE-db.ZIP
MIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
This is perfect, thank you so much!  Thanks, as well, for all of the commented out code...really helps me learn as I go.
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
;-)
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.