Mail Merge issue with data format

[Silicor]

Access 2007

I have a data set that contains an (application, person, role). There is a one to many relationship [application] to [person].

app A, Bob, Owner
app A, Tom, Owner
App A, Bill, Developer
App B, Tom, Owner

My goal is to do an email merge into word. I'm fine if all the [person] get the email, but my problem is that I want to include a list of all the [person] for each application on each email

Application: App A
Recipient: Bob
Application Team: Bob,Tom, Bill

There is not a fixed number of members in each application team.

What is the best way to approach this issue. There seem to be two possible choices: 1. create a singe field with the application team in Access 2. Use word rules to do this upon merge. I've done some research but I can't figure out a straight forward way to do this (I'm pretty much a beginner with vb code)

[nico5038]

Guess you'll need a roll up function, check this code:

Function fncRollUpField(strApplication as string) as string
' function to append multiple fields into one
' save function this in a module
' use in a query like:
' select fncRollUpField(application) from tblYours
 
Dim rs As DAO.Recordset
 
fncRollUpField = ""
 
Set rs = CurrentDb.OpenRecordset("select * from tblYours where application='" & strApplication & "'")
' nothing found
if rs.eof and rs.bof then
   exit function
end if
' process records found
While Not rs.EOF
   fncRollUpField = fncRollUpField & ", " & rs!application
   rs.MoveNext
Wend
 
' remove first ", "
fncRollUpField = mid(fncRollUpField,3)
 
End Function
 

Nic;o)

[Silicor]

Great response, thanks! I'll see if I can get this working

[nico5038]

Keep me posted :-)

Nic;o)