Solved

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

Posted on 2009-05-04
6
296 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Office 365 Training for IT Pros

Learn how to provision Office 365 tenants, synchronize your on-premise Active Directory, and implement Single Sign-On.

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

738 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