[Peter Hibbs[_2_]]

Office 2003.

I am trying to create a Mail Merge document using Access for an
insurance broker. The data to be merged is in an Access table and
there is one record for each Customer/Policy . The problem is that
there may be more than one Policy for any given customer, so the data
in the table (simplified for this post) will look something like :-

Client Name Client Address Policy No Amount Due
-------------- ------------- ------------- -----------
Mr J Bloggs 34 High Street XYZ123456 £200.00
Mr J Bloggs 34 High Street ABC777999 £450.00
Mr J Bloggs 34 High Street Z12345T88 £823.00
Mr A Smith 64 Acacia Ave SM836464 £435.00
Mr A Smith 64 Acacia Ave SM0925337 £189.00

The records are sorted on customer surname and initials so will always
be grouped together like this.

The problem is that, if I just include the fields above in a standard
Mail Merge document, Mr Bloggs will receive three separate letters and
Mr Smith will receive two letters. What I need to do is to send ONE
letter to each customer and include the multiple lines of policy
information in the same letter.

So if the Mail Merge Master looks something like :-
____________________________________________
Title Initials Surname
CustomerAddress

The following policies are due for renewal this month -

Policy Number: PolicyNo
Premium Due: AmountDue
____________________________________________

The merged letter would then look something like :-
____________________________________________
Mr J Bloggs
34 High Street

The following policies are due for renewal this month -

Policy Number: XYZ123456
Premium Due: £200.00

Policy Number: ABC777999
Premium Due: £450.00

Policy Number: Z12345T88
Premium Due: £823.00
____________________________________________
Next letter for Mr A Smith follows here and so on :---


Is there any mechanism in Word that if certain fields are repeated in
a table, it will NOT create a new letter but still add the
non-repeated fields to the same letter. If not, is there any way it
could be done in the Access table,(I have complete control over what
is stored in the table). I'm sure this sort of thing must be fairly
commonplace in mail merge operations.

Peter Hibbs.

[Graham Mayor]

How to use mail merge to create a list sorted by category -
http://support.microsoft.com/?kbid=294686

Fellow MVP 'macropod' has posted a tutorial on this, with working field
codes and a sample Excel data source, at:
http://www.wopr.com/cgi-bin/w3t/show...?Number=731107


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Peter Hibbs wrote:
Office 2003.

I am trying to create a Mail Merge document using Access for an
insurance broker. The data to be merged is in an Access table and
there is one record for each Customer/Policy . The problem is that
there may be more than one Policy for any given customer, so the data
in the table (simplified for this post) will look something like :-

Client Name Client Address Policy No Amount Due
-------------- ------------- ------------- -----------
Mr J Bloggs 34 High Street XYZ123456 £200.00
Mr J Bloggs 34 High Street ABC777999 £450.00
Mr J Bloggs 34 High Street Z12345T88 £823.00
Mr A Smith 64 Acacia Ave SM836464 £435.00
Mr A Smith 64 Acacia Ave SM0925337 £189.00

The records are sorted on customer surname and initials so will always
be grouped together like this.

The problem is that, if I just include the fields above in a standard
Mail Merge document, Mr Bloggs will receive three separate letters and
Mr Smith will receive two letters. What I need to do is to send ONE
letter to each customer and include the multiple lines of policy
information in the same letter.

So if the Mail Merge Master looks something like :-
____________________________________________
Title Initials Surname
CustomerAddress

The following policies are due for renewal this month -

Policy Number: PolicyNo
Premium Due: AmountDue
____________________________________________

The merged letter would then look something like :-
____________________________________________
Mr J Bloggs
34 High Street

The following policies are due for renewal this month -

Policy Number: XYZ123456
Premium Due: £200.00

Policy Number: ABC777999
Premium Due: £450.00

Policy Number: Z12345T88
Premium Due: £823.00
____________________________________________
Next letter for Mr A Smith follows here and so on :---


Is there any mechanism in Word that if certain fields are repeated in
a table, it will NOT create a new letter but still add the
non-repeated fields to the same letter. If not, is there any way it
could be done in the Access table,(I have complete control over what
is stored in the table). I'm sure this sort of thing must be fairly
commonplace in mail merge operations.

Peter Hibbs.

[Doug Robbins - Word MVP]

Why don't you use a Report in Access where you group on the Customer field.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Peter Hibbs" wrote in message
...
Office 2003.

I am trying to create a Mail Merge document using Access for an
insurance broker. The data to be merged is in an Access table and
there is one record for each Customer/Policy . The problem is that
there may be more than one Policy for any given customer, so the data
in the table (simplified for this post) will look something like :-

Client Name Client Address Policy No Amount Due
-------------- ------------- ------------- -----------
Mr J Bloggs 34 High Street XYZ123456 £200.00
Mr J Bloggs 34 High Street ABC777999 £450.00
Mr J Bloggs 34 High Street Z12345T88 £823.00
Mr A Smith 64 Acacia Ave SM836464 £435.00
Mr A Smith 64 Acacia Ave SM0925337 £189.00

The records are sorted on customer surname and initials so will always
be grouped together like this.

The problem is that, if I just include the fields above in a standard
Mail Merge document, Mr Bloggs will receive three separate letters and
Mr Smith will receive two letters. What I need to do is to send ONE
letter to each customer and include the multiple lines of policy
information in the same letter.

So if the Mail Merge Master looks something like :-
____________________________________________
Title Initials Surname
CustomerAddress

The following policies are due for renewal this month -

Policy Number: PolicyNo
Premium Due: AmountDue
____________________________________________

The merged letter would then look something like :-
____________________________________________
Mr J Bloggs
34 High Street

The following policies are due for renewal this month -

Policy Number: XYZ123456
Premium Due: £200.00

Policy Number: ABC777999
Premium Due: £450.00

Policy Number: Z12345T88
Premium Due: £823.00
____________________________________________
Next letter for Mr A Smith follows here and so on :---


Is there any mechanism in Word that if certain fields are repeated in
a table, it will NOT create a new letter but still add the
non-repeated fields to the same letter. If not, is there any way it
could be done in the Access table,(I have complete control over what
is stored in the table). I'm sure this sort of thing must be fairly
commonplace in mail merge operations.

Peter Hibbs.

[Peter Hibbs[_2_]]

Graham,

Thanks very much for that info, it does look like it will do what we
want, I will have to study it further.

Peter Hibbs.

On Mon, 16 Feb 2009 11:22:25 +0200, "Graham Mayor"
wrote:

How to use mail merge to create a list sorted by category -
http://support.microsoft.com/?kbid=294686

Fellow MVP 'macropod' has posted a tutorial on this, with working field
codes and a sample Excel data source, at:
http://www.wopr.com/cgi-bin/w3t/show...?Number=731107

[Peter Hibbs[_2_]]

Doug,

That is an option, of course. The problem would be that the user will
want to use some formatting and, perhaps, need to change the layout
for the document each time. As I am writing the database for the
insurance company that would be a bit impractical as I would have to
change the report design each time. Having said that, it may be
possible to design a general purpose report for this particular
function which would do what they want, and it would be a lot easier
to do than teaching them how to design complex mail merge master
documents. I will see what they have to say.

Peter Hibbs.

On Mon, 16 Feb 2009 19:32:51 +1000, "Doug Robbins - Word MVP"
wrote:

Why don't you use a Report in Access where you group on the Customer field.

[omsoft]

I am dealing with a similar issue. I have for each customer their product,
revenue info. I have created a table in Access with one row per customer and
five pairs of fields in that row for product and revenue. This allows me to
map one record per one letter.

I am trying to now figure out how to not show blank row where revenue = 0.

"Peter Hibbs" wrote:

Office 2003.

I am trying to create a Mail Merge document using Access for an
insurance broker. The data to be merged is in an Access table and
there is one record for each Customer/Policy . The problem is that
there may be more than one Policy for any given customer, so the data
in the table (simplified for this post) will look something like :-

Client Name Client Address Policy No Amount Due
-------------- ------------- ------------- -----------
Mr J Bloggs 34 High Street XYZ123456 £200.00
Mr J Bloggs 34 High Street ABC777999 £450.00
Mr J Bloggs 34 High Street Z12345T88 £823.00
Mr A Smith 64 Acacia Ave SM836464 £435.00
Mr A Smith 64 Acacia Ave SM0925337 £189.00

The records are sorted on customer surname and initials so will always
be grouped together like this.

The problem is that, if I just include the fields above in a standard
Mail Merge document, Mr Bloggs will receive three separate letters and
Mr Smith will receive two letters. What I need to do is to send ONE
letter to each customer and include the multiple lines of policy
information in the same letter.

So if the Mail Merge Master looks something like :-
____________________________________________
Title Initials Surname
CustomerAddress

The following policies are due for renewal this month -

Policy Number: PolicyNo
Premium Due: AmountDue
____________________________________________

The merged letter would then look something like :-
____________________________________________
Mr J Bloggs
34 High Street

The following policies are due for renewal this month -

Policy Number: XYZ123456
Premium Due: £200.00

Policy Number: ABC777999
Premium Due: £450.00

Policy Number: Z12345T88
Premium Due: £823.00
____________________________________________
Next letter for Mr A Smith follows here and so on :---


Is there any mechanism in Word that if certain fields are repeated in
a table, it will NOT create a new letter but still add the
non-repeated fields to the same letter. If not, is there any way it
could be done in the Access table,(I have complete control over what
is stored in the table). I'm sure this sort of thing must be fairly
commonplace in mail merge operations.

Peter Hibbs.