Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-12-2020, 06:56 PM
SQUiD SQUiD is offline Mail Merge - Multiple Sources Windows 10 Mail Merge - Multiple Sources Office 2019
Novice
Mail Merge - Multiple Sources
 
Join Date: Nov 2020
Posts: 6
SQUiD is on a distinguished road
Unhappy Mail Merge - Multiple Sources

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
File Type: xlsx Source 1 - Access data.xlsx (9.5 KB, 2 views)
File Type: xlsx Source 2 - Excel data.xlsx (11.5 KB, 2 views)
Reply With Quote
  #2  
Old 11-13-2020, 02:04 PM
Charles Kenyon Charles Kenyon is offline Mail Merge - Multiple Sources Windows 10 Mail Merge - Multiple Sources Office 2019
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 7,103
Charles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to behold
Default

Post deleted as incorrect by Charles Kenyon

Last edited by Charles Kenyon; 11-16-2020 at 06:43 AM.
Reply With Quote
  #3  
Old 11-13-2020, 02:52 PM
macropod's Avatar
macropod macropod is offline Mail Merge - Multiple Sources Windows 10 Mail Merge - Multiple Sources Office 2010
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,726
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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]
Reply With Quote
  #4  
Old 11-15-2020, 07:44 PM
SQUiD SQUiD is offline Mail Merge - Multiple Sources Windows 10 Mail Merge - Multiple Sources Office 2019
Novice
Mail Merge - Multiple Sources
 
Join Date: Nov 2020
Posts: 6
SQUiD is on a distinguished road
Default

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.


Code:
{ DATABASE  \d "\\\\Nas\\xx\\Test\\External Plant Hire v2.1.accdb" \s "SELECT `EquipmenttMake`, `EquipmentModel`, `EquipmentYear`, `EquipmentRegistration` FROM `qryConfirmationOrderExport`" }

I was actually attempting to replicate your Incident document from that other thread :P
Reply With Quote
  #5  
Old 11-15-2020, 11:47 PM
macropod's Avatar
macropod macropod is offline Mail Merge - Multiple Sources Windows 10 Mail Merge - Multiple Sources Office 2010
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,726
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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]
Reply With Quote
  #6  
Old 11-16-2020, 06:42 AM
Charles Kenyon Charles Kenyon is offline Mail Merge - Multiple Sources Windows 10 Mail Merge - Multiple Sources Office 2019
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 7,103
Charles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to beholdCharles Kenyon is a splendid one to behold
Default

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

If Paul tells you something about mail merge that contradicts something I say, believe Paul.
Reply With Quote
  #7  
Old 11-16-2020, 02:31 PM
SQUiD SQUiD is offline Mail Merge - Multiple Sources Windows 10 Mail Merge - Multiple Sources Office 2019
Novice
Mail Merge - Multiple Sources
 
Join Date: Nov 2020
Posts: 6
SQUiD is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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.
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
File Type: zip Confirmation Order.zip (490.7 KB, 3 views)
Reply With Quote
  #8  
Old 11-16-2020, 02:32 PM
SQUiD SQUiD is offline Mail Merge - Multiple Sources Windows 10 Mail Merge - Multiple Sources Office 2019
Novice
Mail Merge - Multiple Sources
 
Join Date: Nov 2020
Posts: 6
SQUiD is on a distinguished road
Default

Quote:
Originally Posted by Charles Kenyon View Post
Short note.
I keep learning. Sorry for the misinformation.

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

Thanks mate, appreciate it
Reply With Quote
  #9  
Old 11-16-2020, 02:40 PM
macropod's Avatar
macropod macropod is offline Mail Merge - Multiple Sources Windows 10 Mail Merge - Multiple Sources Office 2010
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,726
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Quote:
Originally Posted by SQUiD View Post
In terms of the WHERE, do you mind providing an example?
Have a look at post #4 of the thread you linked to.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #10  
Old 11-16-2020, 03:05 PM
SQUiD SQUiD is offline Mail Merge - Multiple Sources Windows 10 Mail Merge - Multiple Sources Office 2019
Novice
Mail Merge - Multiple Sources
 
Join Date: Nov 2020
Posts: 6
SQUiD is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
Have a look at post #4 of the thread you linked to.

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
Reply With Quote
  #11  
Old 11-16-2020, 03:17 PM
SQUiD SQUiD is offline Mail Merge - Multiple Sources Windows 10 Mail Merge - Multiple Sources Office 2019
Novice
Mail Merge - Multiple Sources
 
Join Date: Nov 2020
Posts: 6
SQUiD is on a distinguished road
Default

Quote:
Originally Posted by SQUiD View Post
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.

Reply With Quote
  #12  
Old 11-16-2020, 06:27 PM
macropod's Avatar
macropod macropod is offline Mail Merge - Multiple Sources Windows 10 Mail Merge - Multiple Sources Office 2010
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,726
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mail merge with 2 sources Marcia Mail Merge 3 07-04-2020 09:38 PM
Mail merge - data sources Sarki76 Mail Merge 2 09-02-2019 01:28 AM
Mail Merge - Multiple Sources Mail Merge Student Transcript - Multiple Years from multiple sheets aizah2018 Mail Merge 1 11-11-2018 01:27 AM
Choosing Mail Merge templates and excel Data Sources using drop downs dushan997 Mail Merge 3 08-04-2017 07:54 PM
Mail Merge to multiple employees in multiple locations as PDF by location GFiz Mail Merge 1 05-13-2017 08:01 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 06:13 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft