By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,896 Members | 1,997 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,896 IT Pros & Developers. It's quick & easy.

Mail Merge issue with data format

P: 2
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)
Dec 5 '08 #1
Share this Question
Share on Google+
3 Replies

Expert 2.5K+
P: 3,072
Guess you'll need a roll up function, check this code:
Expand|Select|Wrap|Line Numbers
  1. Function fncRollUpField(strApplication as string) as string
  2. ' function to append multiple fields into one
  3. ' save function this in a module
  4. ' use in a query like:
  5. ' select fncRollUpField(application) from tblYours
  7. Dim rs As DAO.Recordset
  9. fncRollUpField = ""
  11. Set rs = CurrentDb.OpenRecordset("select * from tblYours where application='" & strApplication & "'")
  12. ' nothing found
  13. if rs.eof and rs.bof then
  14.    exit function
  15. end if
  16. ' process records found
  17. While Not rs.EOF
  18.    fncRollUpField = fncRollUpField & ", " & rs!application
  19.    rs.MoveNext
  20. Wend
  22. ' remove first ", "
  23. fncRollUpField = mid(fncRollUpField,3)
  25. End Function
Dec 8 '08 #2

P: 2
Great response, thanks! I'll see if I can get this working
Dec 8 '08 #3

Expert 2.5K+
P: 3,072
Keep me posted :-)

Dec 8 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.