Hi all


I believe what I'm attempting to achieve is similar to that in the below thread, however I haven't been able to successfully reproduce.
https://www.msofficeforums.com/mail-...-multiple.html



What I'm trying to achieve is to create one word document/letter that has been mail merged with two sources, that both contain a unique identifier to link them.


Source 1 - Content contained in a Microsoft Access table contains the the first components of the letter;



Source 2 - An excel file, that is stored locally. There is additional information to form the second component of the letter.



I am attempting to create the first part with Access info. directly from Access' merge tool then complete the second component using 'database' fields.


I'm open to doing whatever is required to achieve this as currently, I'm exporting the Access data to excel, then copy + pasting that data into source 2's excel file.

Attached Files

	Source 1 - Access data.xlsx (9.5 KB, 9 views)
	Source 2 - Excel data.xlsx (11.5 KB, 10 views)

Post deleted as incorrect by Charles Kenyon

Neither of the addins referred to by Charles will work in this situation. And, contrary, to what Charles says, Word can "do this out of the box".

The DATABASE field approach referred to in the thread you linked to is the correct way to address the issue.

Without seeing your mailmerge main document, however, it is quite impossible to identify or resolve the problem.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thanks for the response, I've uploaded a copy of what the document will generally look like (excuse the existing merge fields).

The fields highlighted in yellow is the information that's being pulled from a m$ database query, whilst the fields highlighted in green is from excel.

The fields in both sources will never alter field names.

It'll only ever be generation of one letter at a time, at each time a request it placed, I'll look to create this merged letter.

-----------------------------------------------------------

The issue I'm experiencing with the database fields is its not recognising the source and returns an error, no valid result from table, the below is what I've worked out so far which is displaying results, however when I go to duplicate this elsewhere calling upon different fields, I experience issues.



I was actually attempting to replicate your Incident document from that other thread :P

There is no document attached to you post.

As for your DATABASE field representation, it lacks a 'WHERE' condition with both the reference to either the mergefield you're trying to match with and the corresponding field in the secondary dataset. Moreover, it doesn't refer to either of your Excel workbooks but to your Access database.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Short note.
I keep learning. Sorry for the misinformation.

If Paul tells you something about mail merge that contradicts something I say, believe Paul.

Hopefully it attaches this time , I've added it to a zip as it was slightly to large.

Just note that I haven't added any database references to it yet.

So I would use the database fields to reference the excel spreadsheet and the access database, thats good to know.

In terms of the WHERE, do you mind providing an example?

Attached Files

Confirmation Order.zip (490.7 KB, 15 views)

Thanks mate, appreciate it

Have a look at post #4 of the thread you linked to.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hopefully I haven't caused confusion.


The first source is a database (I just extracted as a spreadsheet to show the field names) and the secondary source is an excel spreadsheet.


{DATABASE \d "\\\Nas\\13 Test\\External Plant Hire v2.1.accdb" \s " SELECT `HireID` AS `Hire Reference` FROM `qryConfirmationOrderExport` WHERE [ID] = {MERGEFIELD ID}" \l "15" \b "49" \h}


^ Just testing with pulling one field currently to ensure it works before pulling others, I experience an error with the WHERE component, I'm unsure what to specify exactly.


Thanks for your patience

An update after playing around a little, this returns results -


{DATABASE \d "\\\Nas\\13 Test\\External Plant Hire v2.1.accdb" \s " SELECT `HireID` AS `Hire Reference` FROM `qryConfirmationOrderExport` WHERE `HireID` = 4" \l "15" \b "49" \h}


However:
* After every refresh I have to select the database source for each field;
* With the WHERE field, can I have a wild card instead of a specific reference; and
* Is there a string I can use to allow the field code to be nested within an existing table - I don't need it to create its own table, just the content.

I am quite unable to correlate your document with the Excel workbooks you attached to post #1. There are numerous field references that relate to neither workbook. Moreover, your DATABASE field depiction is still trying to reference an Access database instead of your 'Source 2 - Excel data' workbook and refers to multiple fields that don't exist in that workbook.

You're really not helping anyone resolve your problem.

Try the following DATABASE field representation, which references your 'Source 2 - Excel data' workbook. It is assumed your mailmerge main document and the workbook are stored in the same folder.

{DATABASE \d "{FILELAME \p}/../Source 2 - Excel data.xlsx" \s "SELECT `Email`, `Name`, `Q1 Y/N`, `Q2 Text`, `Hire Reference` FROM [Form1$] WHERE {={ MERGEFIELD Hire_Reference} \# 0} = `Hire Reference`" \l "23" \b "47" \h}

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]