JCJG
asked on
Word Mail Merge using data with one to many relationship
Hi, I have to create a letter in Word document using data from Excel. or Access The letter address to each student with a list of classes enrolled and number of units for each class. One student may enroll in multiple classes.
What format do I need the data (in either Excel or Access) and how to set up the merge field in Word? It seems to me using "NEXTIF" might work but I am totally new to mail merge. I'd appreciate your guidance. Thanks.
What format do I need the data (in either Excel or Access) and how to set up the merge field in Word? It seems to me using "NEXTIF" might work but I am totally new to mail merge. I'd appreciate your guidance. Thanks.
Mailmerge doesn't cater for one-to-many or hierarchical recordsets. I would put it high on the list of candidates for Word improvements.
There are a couple of techniques that do not use VBA.
If you have a limited number of subsidiary items, you can arrange your datasource so that each item has its own field.
e.g.
Forename, Surname, address1, address2, postcode, item1, item2, item3, item4,... itemN.
Another is to use Word Fields IF and MERGESEQ
http://support.microsoft.com/kb/294686/
Here are four approaches that use VBA.
This one uses VBA (in Access, but it could be in Word) to create an individual document per customer.
https://www.experts-exchange.com/questions/21090605/Merging-and-Access-Query.html
This talks about hooking into the MailMergeAfterRecordMerge event to create a recordset for each customer's orders.
https://www.experts-exchange.com/questions/21478463/Great-new-way-to-Mail-merge-one-to-many-records-fails-at-final-hurdle.html
Here VBA is used to split a single-table merge output into individual tables.
https://www.experts-exchange.com/questions/21633674/Merge-rows-of-excel-condition.html
This one is a Directory type merge to a Word table. The datasource is a flat file or the result of a one-to-many query. The merge fields are set up in the Word table to receive the 'many' data.
The MailMergeBeforeRecordMerge event is used to look for a change of data in the One table. It then starts a new document Section, saves the data from the one side fields using Word VBA techniques, and carries on until the next change.
https://www.experts-exchange.com/questions/21669663/Need-help-with-complicated-mail-merge-with-Excel-spreadsheet.html
There are a couple of techniques that do not use VBA.
If you have a limited number of subsidiary items, you can arrange your datasource so that each item has its own field.
e.g.
Forename, Surname, address1, address2, postcode, item1, item2, item3, item4,... itemN.
Another is to use Word Fields IF and MERGESEQ
http://support.microsoft.com/kb/294686/
Here are four approaches that use VBA.
This one uses VBA (in Access, but it could be in Word) to create an individual document per customer.
https://www.experts-exchange.com/questions/21090605/Merging-and-Access-Query.html
This talks about hooking into the MailMergeAfterRecordMerge event to create a recordset for each customer's orders.
https://www.experts-exchange.com/questions/21478463/Great-new-way-to-Mail-merge-one-to-many-records-fails-at-final-hurdle.html
Here VBA is used to split a single-table merge output into individual tables.
https://www.experts-exchange.com/questions/21633674/Merge-rows-of-excel-condition.html
This one is a Directory type merge to a Word table. The datasource is a flat file or the result of a one-to-many query. The merge fields are set up in the Word table to receive the 'many' data.
The MailMergeBeforeRecordMerge
https://www.experts-exchange.com/questions/21669663/Need-help-with-complicated-mail-merge-with-Excel-spreadsheet.html
An aside...
GrahamSkan,
That last link to your previous question looks to be the sort of thing that's wanted here. Perhaps one day I'll learn to automate Word merges from Excel data - I'll have to study your code.
Patrick
GrahamSkan,
That last link to your previous question looks to be the sort of thing that's wanted here. Perhaps one day I'll learn to automate Word merges from Excel data - I'll have to study your code.
Patrick
Hello JCJG,
Jeff's suggestion of concatenating the results (perhaps using the good ol' Chr(13) & Chr(10) as the delimiter)
is the approach I would have taken if forced at gunpoint to do this with a MailMerge. (And I am with Graham--
the ability to do a one-to-many detail merge would be very useful!)
However, I would encourage you to think about alternatives. For myself, I would try to do this by creating an
Access report. Access is built to handle a one-to-many relationship...
Regards,
Patrick
Jeff's suggestion of concatenating the results (perhaps using the good ol' Chr(13) & Chr(10) as the delimiter)
is the approach I would have taken if forced at gunpoint to do this with a MailMerge. (And I am with Graham--
the ability to do a one-to-many detail merge would be very useful!)
However, I would encourage you to think about alternatives. For myself, I would try to do this by creating an
Access report. Access is built to handle a one-to-many relationship...
Regards,
Patrick
matthewspatrick:,
"perhaps using the good ol' Chr(13) & Chr(10) as the delimiter)"
Actually I was impliying a "Horizontal" concatenation:
(Something like this)
strClassList=strClassList & ", " me.ClassList...
...In a loop, as to accumulate the classes.
This is a common request I get in Access Reports (Horizontal Fields) to save vertical space on the report.
But now that you mention it, "stacking" them in a list, might be a better option...
;-)
Jeff
"perhaps using the good ol' Chr(13) & Chr(10) as the delimiter)"
Actually I was impliying a "Horizontal" concatenation:
(Something like this)
strClassList=strClassList & ", " me.ClassList...
...In a loop, as to accumulate the classes.
This is a common request I get in Access Reports (Horizontal Fields) to save vertical space on the report.
But now that you mention it, "stacking" them in a list, might be a better option...
;-)
Jeff
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I appreciate all your input. The Access option is definitely a good alternative.
I have tried to find a way to do it in Word and have come across the following link. Unfortunately, I don't know how to make it work.
http://office.microsoft.com/en-us/word/HP100779671033.aspx
I have tried to find a way to do it in Word and have come across the following link. Unfortunately, I don't know how to make it work.
http://office.microsoft.com/en-us/word/HP100779671033.aspx
JCJG,
I can't see clearly how NEXTIF could be easily applied to this situation.
(One to many, related data)
JeffCoachman
I can't see clearly how NEXTIF could be easily applied to this situation.
(One to many, related data)
JeffCoachman
ASKER
Thanks for your help!
;-)
I will presume that the Student Info is in one table and the class info is in another?
You have to create a query joining the two tables.
Then create a fi\unction that loops each class for every student and concatenates them to gether as one string, to be easilly inserted as a mail merge Filed.
I am heding for bed now.
Another expert may be willing to help you.
If not, post back, and I will see what I can do.
JeffCoachman