?
Solved

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

Posted on 2009-05-04
6
Medium Priority
?
300 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

718 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