Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

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

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
0
setalley
Asked:
setalley
  • 3
  • 2
1 Solution
 
coffeeshopCommented:
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.


0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
setalleyAuthor 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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jeffrey CoachmanMIS LiasonCommented:
Try This:
NewPDE-db.mdb
0
 
setalleyAuthor 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.
0
 
Jeffrey CoachmanMIS LiasonCommented:
;-)
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now