Help with table design to support concatenate function

[BillMcD37]

I am building a database that collects stakeholder email addresses for projects with the goal of automating the distribution of monthly reports for each project to those stakeholders.

I have three tables:
Project Master (primary key=Project ID)
description, project category, etc.
Stakeholder Master (primary key=Stakeholder ID)
Last name, first name, email address
Xref file (Primary key=automatic, with foreign keys to Project Master and Stakeholder master
Project ID, Stakeholder ID, Stakeholder type (a stakeholder might be a principal on one project, but an 'interested party' on another)

The data in the Xref table looks like this:
Project ID, Stakeholder ID, Stakeholder Type
12345, Abcd, Y3
12345, xyzz, Y4
12345, dddd, Y1
12345, ffff, Y2
12345, gggg, Y1

I want to be able to output the data to look like this:

12345, Y1:gggg;dddd, Y2:ffff; Y3:Abcd, Y4: xyzz

Depending on the type of project, the emails might go to :all stakeholder types, only Y3's, or Y1's and Y3's (there are a limited number of distribution rules, but more that one)

I am stuck with how to build this output. It looks like I have to build concatenated strings of Stakeholder IDs by Stakeholder type for each project ID.

I've tried a couple of the concatenation VBA's out, but I cannot make any of them work the way that I want them to.

Does anyone have any suggestions on an approach to building the strings of stakeholder IDs? Ultimately, I plan to pass these strings out to a Mail Merge in Word/Outlook, but I need to get this one piece of design figured out first.

Thanks in advance.

Bill:banghead:

 

[Alansidman]

This should be helpful

http://allenbrowne.com/ser-67.html

 

[BillMcD37]

It sure is! Thanks! I'll play with this for a bit. I already got it to count the number of stockholders in each category by Project ID. Now I just need to get the emails into the columns...

 

[BillMcD37]

The crosstab query will only allow me to see the first value or the last value in a category, how do I get at the values that are not the first or the last?

 

[Alansidman]

Look at this tutorial and see if it helps you. It is an alternative means of cross tab queries.

http://www.datapigtechnologies.com/flashfiles/crosstab.html

 

[jdraw]

In addition to Alan's suggestions, you could also look at
http://allenbrowne.com/func-concat.html and see if it may help.

 

[BillMcD37]

Thanks! I am using Allen Browne's ConcatRelated function successfully with an approach that uses crosstabs to build a record for each program with the stakeholder functions as columns in the crosstab. It's not elegant but it's working for me.

Thanks for the help.

 

[jdraw]

Glad you have it working. If your code is somewhat unusual but fills a general need, you may wish to post the code.

 

[BillMcD37]

Thanks again for your help. I am not sure that my solution is very elegant, but it's working.

I'll keep looking at these forums.