Mail Merge - of table
Say, I've an Access table in Access 2013 and they structure is as follows : Name, Surname and Volunteer.
The Name and Surname are repeated exactly but Volunteer changes at each instance ie. for each name and surname there are a number of volunteers. Sample table follows :
Name Surn Volunteer
Peter Smith Harry
Peter Smith Jeff
Peter Smith Archie
William Jones Shaun
William Jones Neville
Say, How can I use Word 2013 Mail Merge to Merge a single document for each unique name with the corresponding Volunteers? I was thinking of creating a 2nd table with the names and joining the two tables with the query as data source for the merge document. Would this work? How do I merge from a query?
The Name and Surname are repeated exactly but Volunteer changes at each instance ie. for each name and surname there are a number of volunteers. Sample table follows :
Name Surn Volunteer
Peter Smith Harry
Peter Smith Jeff
Peter Smith Archie
William Jones Shaun
William Jones Neville
Say, How can I use Word 2013 Mail Merge to Merge a single document for each unique name with the corresponding Volunteers? I was thinking of creating a 2nd table with the names and joining the two tables with the query as data source for the merge document. Would this work? How do I merge from a query?
Ultimately you would need two tables:
tblEmp
eID
eFN
eLN
tblVolunteers
vID
v_eID
eFN
eLN
Then you can join them in a query
Then create a "Grouped" report
tblEmp
eID
eFN
eLN
tblVolunteers
vID
v_eID
eFN
eLN
Then you can join them in a query
Then create a "Grouped" report
Sample db attached.
Note the you can also do things like:
Moving the filed around
concatenating the first and last names
adding headings
having counts of volunteers
...etc
But I have presented the basics here...
JeffCoachman
Database51.mdb
Note the you can also do things like:
Moving the filed around
concatenating the first and last names
adding headings
having counts of volunteers
...etc
But I have presented the basics here...
JeffCoachman
Database51.mdb
ASKER
Jeffrey Coachman, much appreciated.
Group Report is in ms access I presume. This is an option but prefer to use word 2013 as allows easier formatting of the document. Can you explain these steps please? I also need to save each page as a unique file name by ID no so that GroupMail can pick up the file and attach to an email.
Tx
Group Report is in ms access I presume. This is an option but prefer to use word 2013 as allows easier formatting of the document. Can you explain these steps please? I also need to save each page as a unique file name by ID no so that GroupMail can pick up the file and attach to an email.
Tx
Then you have misunderstood what "Merge" means in word.
Mail Merge will "Merge" the existing data into a Word document.
It will not "Merge" the data itself.
So in your case, you cannot use word mail merge alone to get the output you specified.
The Merge data must be in a table format.
One to many relationships (one Employee/many Volunteers), in the format you desire, are not directly supported.
So even if you had two related tables, ...word cannot "Hide" the repeating Employees.
There may be a way to do this in Word, ...but I don't know of anything off hand.
You can do this in Access, ...but it is more of a "Procedure", ...not really steps you can follow like Mail Merge.
I can modify my sample and send you some notes if you like.
let me know.
Jeff
Mail Merge will "Merge" the existing data into a Word document.
It will not "Merge" the data itself.
So in your case, you cannot use word mail merge alone to get the output you specified.
The Merge data must be in a table format.
One to many relationships (one Employee/many Volunteers), in the format you desire, are not directly supported.
So even if you had two related tables, ...word cannot "Hide" the repeating Employees.
There may be a way to do this in Word, ...but I don't know of anything off hand.
You can do this in Access, ...but it is more of a "Procedure", ...not really steps you can follow like Mail Merge.
I can modify my sample and send you some notes if you like.
let me know.
Jeff
Note that it can be done in mail merge, but it is tricky. The link on my comment above is to an earlier question where some methods are outlined. One is a suggestion from Microsoft involving the use of Word field logic. The details are here:
http://support.microsoft.com/kb/294686/
http://support.microsoft.com/kb/294686/
ASKER
Seems Access is simplest way to go.
How can one print a report to pdf - each new page in the report to be printed as a new file. the file name to be the ID. This way can use GroupMail to send off the attachments to their respective email addresses.
How can one print a report to pdf - each new page in the report to be printed as a new file. the file name to be the ID. This way can use GroupMail to send off the attachments to their respective email addresses.
One step at a time...
If you want to go the Access route, then will each "Employee/Volunteers" will be on a page?
If so, then we can loop the Employees and print a PDF report for each employee/volunteer.
Does this sound like what you want?
If so, I can whip up something quick by this afternoon.
JeffCoachman
If you want to go the Access route, then will each "Employee/Volunteers" will be on a page?
If so, then we can loop the Employees and print a PDF report for each employee/volunteer.
Does this sound like what you want?
If so, I can whip up something quick by this afternoon.
JeffCoachman
ASKER
tx, yes the Access Route seems simplest and as you've described - but open to any other suggestions as to how to go about this.
These are great suggestions:
concatenating the first and last names
adding headings
having counts of volunteers
tx
These are great suggestions:
concatenating the first and last names
adding headings
having counts of volunteers
tx
all doable...
ASKER
Would appreciate if can wip it together please Jeffrey. PS this is to assist us to send Volunteers to visit Senior Citizens. Shaun
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I am speaking as an OAP, and a Word expert, so I hopethat my contribution s acceptable.
Jeffrey's suggestion is the simpler alternative between a difficult Mail Merge and an Access report. However, if you want the superior formatting possibilities of Word then you can import the Access report into Word and then apply your custom formatting.
Jeffrey's suggestion is the simpler alternative between a difficult Mail Merge and an Access report. However, if you want the superior formatting possibilities of Word then you can import the Access report into Word and then apply your custom formatting.
starting point:
ASKER
Can you suggest a simple graphic tool to post a clear picture of what is required?
ASKER
Perhaps you can re-read my requests and incorporate your suggestions these are great. If you send me the report - I can modify it accordingly to tailor it. tx Shaun
I would recommend using another method than mail merge. You need to merge data from a main table and a linked table (since the tables are not distinct, you can use queries to separate the data into two groups). See my old Four Ways to Merge to Word sample database:
http://www.helenfeddema.com/Files/code24.zip
or my recently updated 4th edition of my Working with Word ebook (available from the Office Watch Website), which covers Office versions through 2013.
One of the examples demonstrates merging from linked tables, using doc properties and TypeText.
http://www.helenfeddema.com/Files/code24.zip
or my recently updated 4th edition of my Working with Word ebook (available from the Office Watch Website), which covers Office versions through 2013.
One of the examples demonstrates merging from linked tables, using doc properties and TypeText.
ASKER
Tx Helen
Can you perhaps send a sample implementation using the data I supplied?
Can you perhaps send a sample implementation using the data I supplied?
That is a very small data sample -- I can add some more rows for testing. I will see if I can come up with something later today.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I tried repeatedly, but EE wouldn't upload the small .accdb database, either as is or zipped. I will try to upload it later today, or maybe I could email it to you.
ASKER
Link giving an error I'm afraid
Trying to upload here once more.
Test-Merge.accdb
Test-Merge.accdb
It is working today!
ASKER
Tx. Downloaded and hope to get to the PC with ACCESS soon.
--------------------------
Name: Peter Smith
Volunteers:
Harry
Jeff
--------------------------
Name: William Jones
Volunteers:
Shaun
Neville
--------------------------
If so, be aware that the mail merge basic design expects to work with a single table-like structure. Yours is a one-to-many situation requiring two tables to be joined in an hierarchical dataset.
There are several workarounds discussed in the following link. The best depends on your exact situation and skill set.
https://www.experts-exchange.com/questions/23658368/Doing-a-mail-merge-with-section-breaks-using-a-specific-value.html