Word Mailmerge multiple records per record... 1

[xplo5iv]

I have an excel source with the following headings

Staff Number
Name
Allowance
Amount

Each employee appears one or more times, as they can have more than one allowance. The list is sorted by staff number, so the records are together for each staff member.

I need to create a document per person which includes Staff Number & Name once, followed by a list of allowances and amounts e.g.

Staff Number: - 123456
Name: - Fred

Allowances Amounts
Basic Pay 25750
Management 1650
Shift 21750

I can't get my head round how to set up the merge fields. Can anyone help?

Thanks in advance

Jonathan

 

[SkipVought]

Hi,

I'd do it all in Excel. You'll have to record and modify a simple macro.

You will need a unique list of your employees from your source data. This can be done using the Advanced filter - UNIQUE values, no criteria. Copy this unique list to a new, separate sheet.

Use the AutoFilter in your source data, to select an employee. Turn on your macro recorder and record selecting the employee.

Post back with your recorded code in forum707 for help cusomizing.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xplo5iv]

Ok, thanks, posted over on the other forum.

 

[macropod]

Hi Jonathan,

You can use Word's Catalogue/Directory Mailmerge facility for this (the terminolgy depends on the Word version). To see how, check out my Word 97-2007 Catalogue/Directory Mailmerge Tutorial at:

http://www.wopr.com/index.php?showtopic=731107

or

http://www.gmayor.com/Zips/Catalogue Mailmerge.zip

Do read the tutorial before trying to use the mailmerge document included with it.



Cheers
[MS MVP - Word]

 

[xplo5iv]

Fantastic, had a good look through it, exactly what I wanted. Now in the process of converting to suit my needs.

Having seen the complexity of the fields I now know why I couldn't work out how to do it!

Jonathan

 

[macropod]

Hi Jonathan,

Having seen the complexity of the fields

Just wait till you start doing date calculations with fields - that's when they start getting really complicated...


Cheers
[MS MVP - Word]

 

[xplo5iv]

I've now achieved everything I needed, thank you.

For the reference of others who may read this post it is possible to create a grand total of a field/ fields in the upper section to the total of the lower section by using SET

e.g. in my document I have Annual salary in the top part of the document , and a number of allowances in the lower part. If you try adding the salary mergefield to the total you get the wrong record, but by using SET and then adding this bookmark to the total it works.

An extra IF field in the lower section suppresses any zero values


Thanks once again for your help

 

[macropod]

Hi Jonathon,

There are examples in the tutorial showing how to calculate group totals.

An extra IF field in the lower section suppresses any zero values

You can do this without the need for extra fields, via a numeric picture switch. For example, a formula field with a switch coded as '\# 0;;' will display only +ve values; -ve values and 0s will be suppressed.


Cheers
[MS MVP - Word]

 

[xplo5iv]

Ah, thanks

I used the tutorial, and your totalling method for the allowances but my dataset (provided from elsewhere)required a different approach to add the salary on. It was in the format

Name - Salary - Allowance Type - Allowance Value

John - 25000 - Shift - 2500
John - 25000 - Management - 3000
etc

So the salary (25000) appears once in the top part, the allowances appear below, complete with total, (5500) and then a grand total (30500) at the bottom. I found the only way to get this to work was to put the salary value into a bookmark, and use that + the allowance total to get the grand total.

Thanks for everything

Jonathan

 

[xplo5iv]

Just realised re the zero values - I was suppressing the description fields as well, hence the IF. Useful to know for next time, though, Thanks