MS Word Mail Merge Question


I Have a table of data as follows:
Item Code       Desc                   Amount
S-GPS           GPS Device            100 $
S-CBL            GPS Cable              20 $
S-ACS             GPS Accessories 20 $
S-CHR            Fixing Charge        50 $
S-DISC            Discount               -10 $
TOTAL                                       180 $

and I want to print this Invoice using MS Winword mail merge

the point is my invoice printout page is pre-printed forms which has preprinted fields descriptions








From the above list two main notes the order of the pre-printed form of items is not the same as the order of the records (which mainly comes from set of records)

also there are certain items in the pre-printed form might not applicable for this record printout.

how I can design my mail merge form so I can print specific price of a specific item code next to its location in the pre-printed form.

I appreciate if some one can help me ASAP

Who is Participating?
GrahamSkanConnect With a Mentor RetiredCommented:
Actually, going back to the question, I see that although the data in the table that you show is a single table, you would need the each Item code to be a separate field, e.g.:

CustomerID, Customer Name, S-GPS Desc, S-GPS Price,  S-GPL Desc, S-GPL Cde, ...

1234, Smith & Co,  GPS Device,  100 $, GPS Cable 20 $, ...  
5678, Jones& Co,  GPS Device (new type),  110 $, GPS Cable 20 $, ...  

In practice, you are dealing with a set of customers, each of which has a list of items. This requires two linked tables (one-to-many).

Mail merge isn't designed for one-to-many data inputs. It can only deal with a single table or query.

This previous question lists several workarounds:

If you need more help with any of them, please let us know.  
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

I'm afraid you question is not clear. I don't understand this sentence:

>>From the above list two main notes the order of the pre-printed form of items is not the same as the order of the records (which mainly comes from set of records)

In a mail merge, each row of the source data is treated as a new record and will trigger a new document.

Can you post some samples, e.g.

- a sample of the data source, with at least two or three different data sets
- a sample of the merge document as you have it right now
- a manually created document with the desired output.

cheers, teylyn

nadermikAuthor Commented:

I am attaching a sample of the current mail merge and the new mail merge required based on the same records table

and of course i done want a separate page for every record as it is one sales order printout

Note in the file attached the highlighted fields are pre-printed in the form which i want to print on it from the table source

I hope now the request is clear
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Assuming that the sheet shows the layout of the result, and does not resemble the existing records which should each be a single row in your datasource worksheet, then it should be fairly simple to put the fields in the order required.

The difficulty would be in aligning the text to pre-printed stationery.

I suggest that the merge fields be put into textbox shapes (Insert tab,  Text group, Text Box, Draw Text Box), which can be dragged into position. For guidance, you could create a background watermark image of your stationery.
nadermikAuthor Commented:

I am still not clear how to design my mail merge form using the mailing rules that achieves what I am looking at as per the previously attached sheet

can you please clarify
Can you clarify that your actual spreadsheet for the datasource is has a format with one row per record, like a table with the column/field names in the first row?

If so, use the 'Start Mail Merge' button on the Mailings tab to select 'Letters' for the mail merge output layout.

The use the 'Select Recipients' button to connect to the workbook and sheet. You should now be able to use the 'Insert Merge Field' button to place merge fields on the document at the selection point. In your case, you need to position the fields at specific locations on the document, and the easiest way to do that is to insert text boxes and to put the each field within a text box.

Mail Merge reads one record at a time and when it read first record, it only gets Item Descriptioin GPS Device.
If you want to fill description for same customer, you have to convert rows into column as mentioned by GrahamSkan. From that point you can bring all fields for a record into one form.

There is an alternative but then you have to go through hard way; like coding if else and loops

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.