Concatenate records into a single field on a report

[darall]

I have a database that lists projects [tblEngagementData] and the team members assigned to the projects [tblJoin_Engagement_ProjectTeam].

There is a one to many relationship between the Projects and the Team Members tables.

I have created a report that lists each project with a subreport that lists the related team members based on the [strEngagementID] field.

I am looking for a way to make the data display cleaner on the report. In the current format there is a lot of white space since there is a row for each team member.

What I would like to do is find a way to concatenate the related Team Members into one list with commas separating the names (see example below).

Does anyone have any suggestions on how to do this?

Current View:
ID Project Team Members
-- ------- ------------
01 PD Support Jan Smith
Sara Jones
Fred Martin
Alan Choi
David Ni

02 SPD Support Sara Jones
Fred Martin
Greg Long
David Ni


Desired View:
ID Project Team Members
-- ------- ------------
01 PD Support Jan Smith, Sara Jones, Fred Martin, Alan Choi, David Ni

02 SPD Support Sara Jones, Fred Martin, Greg Long, David Ni

 

[RoyVidar]

dhookoms faq faq701-4233 seems to be what you ask for.

Roy-Vidar

 

[darall]

Roy-Vidar,

I tried using FAQ701-4233 but I am not sure I understand what to supply in the «pstrSQL» area of the function.

Can someone give me an example of the expression, the example in the code is incomplete.