Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-05-04
6
Medium Priority
?
303 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
0
Comment
Question by:setalley
  • 3
  • 2
6 Comments
 
LVL 7

Expert Comment

by:coffeeshop
ID: 24296602
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24312179
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
 

Author Comment

by:setalley
ID: 24317886
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 24318951
Try This:
NewPDE-db.mdb
0
 

Author Closing Comment

by:setalley
ID: 31577587
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24321993
;-)
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question