Mail merge query with multiple records

[AlliCarr]

Hi,

I have a mail merge query which allows me to pull all the information I need from one particular parent record on one table and add this to a word document. For each parent record there are up to 3 child records from another table that I also need to add to a table in the word document. When the query runs I get up to 3 versions of the parent record depending on how many child records there are, which is what is expected.

The problem with this is that, when I run the mail merge, I get 3 versions of the word document, one for each of the child records.

Is it possible to build a query which will give me the parent record and all the associated child records in one row and so when I run the mail merge I will get one document with all child records in the appropriate table. I created a cross tab query and this did show all the selected child records in one row but I'm not sure what I need to do now.

I've struggled a little to put into words what I want to do so I hope that makes sense. But if there is any more info needed, please let me know. I have included the query below once it runs in case this helps.

 

[Eugene-LS]

Is it possible to build a query which will give me the parent record and all the associated child records in one row and so when I run the mail merge I will get one document with all child records in the appropriate table. I created a cross tab query and this did show all the selected child records in one row but I'm not sure what I need to do now.

Look for Concatenation ...

 

[Pat Hartman]

This is why I don't use mail merge. I use OLE automation. I build a table in code to hold the many-side data although there are other options as already mentioned.

 

[Gasman]

Probably easier to just use report?

 

[ebs17]

I created a cross tab query and this did show all the selected child records in one row but I'm not sure what I need to do now.

Can you use a cross tab query directly as a source for a mail merge? Probably not.
But you could wrap this query in a select query. Hopefully the PIVOT columns have consistent names.

For higher demands, as already mentioned, automation would be used.
- You use a Word template with bookmarks for the fields of the main record.
- In a loop (through the recordset), copies of the template are now created and the bookmarks are filled.
- Within the loop, a recordset is created with the data of the sub-table. This recordset can be converted directly into a Word table using the Word method ConvertToTable and assigned to a bookmark. The Word table created can be embellished using Word's own formatting methods.

See also note from @Gasman
If your letter can be presented with a report including a sub-report, you would have a simple solution.

 

[mike60smart]

Hi,

I have a mail merge query which allows me to pull all the information I need from one particular parent record on one table and add this to a word document. For each parent record there are up to 3 child records from another table that I also need to add to a table in the word document. When the query runs I get up to 3 versions of the parent record depending on how many child records there are, which is what is expected.

The problem with this is that, when I run the mail merge, I get 3 versions of the word document, one for each of the child records.

Is it possible to build a query which will give me the parent record and all the associated child records in one row and so when I run the mail merge I will get one document with all child records in the appropriate table. I created a cross tab query and this did show all the selected child records in one row but I'm not sure what I need to do now.

I've struggled a little to put into words what I want to do so I hope that makes sense. But if there is any more info needed, please let me know. I have included the query below once it runs in case this helps.
View attachment 115231

As already suggested by Gasman, I would create a Report.

 

[AlliCarr]

Probably easier to just use report?

I had considered this but I need to be able to apply password protection to sections of the document before I email it to the recipient. The recipient then needs to sign it electronically and email it back. I have the version of Adobe Reader that lets me edit PDF documents but none of the users of my database have this so it needs to be in Word.

But you could wrap this query in a select query. Hopefully the PIVOT columns have consistent names.

How would I do this? I did drag the cross tab query into the design grid but couldn't figure out how to connect it to the original select query. I'm assuming I would need to do this in SQL view but I'm pretty inexperienced. The SQL for the main query is:

SELECT GrantsT.OrganisationName, GrantsT.ProjectSummary, GrantsT.FundingRequested,
GrantsT.ApprovalDate, GrantsT.ProjectStartDate, GrantsT.ImpactDueDate,
GrantsT.GrantAmountTotal, GrantsT.TermsandConditions1, GrantsT.TandCDate1,
GrantsT.TermsandConditions2, GrantsT.TandCDate2, GrantsT.TermsandConditions3,
GrantsT.TandCDate3, GrantsT.GrantAmountRevenue, GrantsT.GrantAmountCapital,
GrantsT.GrantContactName, GrantsT.GrantURN, OutcomesT.ListItem,
GrantOutcomesDataT.Selected, GrantOutcomesDataT.Planned
FROM (GrantsT INNER JOIN GrantOutcomesDataT ON GrantsT.GrantURN = GrantOutcomesDataT.DataLinkID)
INNER JOIN OutcomesT ON GrantOutcomesDataT.DataItems = OutcomesT.ListID
WHERE (((GrantOutcomesDataT.Selected)=True));

and the cross tab query is:

TRANSFORM Sum(GrantOutcomesDataT.Planned) AS SumOfPlanned
SELECT GrantOutcomesDataT.Selected
FROM OutcomesT INNER JOIN GrantOutcomesDataT
ON OutcomesT.ListID = GrantOutcomesDataT.DataItems
WHERE (((GrantOutcomesDataT.Selected)=True))
GROUP BY GrantOutcomesDataT.DataLinkID, GrantOutcomesDataT.Selected
PIVOT OutcomesT.ListItem;

Also, in terms of the automation solution, would this work with Albert Kallal's Super Easy Word Merge? There will also be quite a few other documents I need to merge into once the database is live but these won't be in bulk, it will just be as and when needed for single records.

 

[Gasman]

Word (2019) will open pdf files, but convert them to editable format?
This is a report from Xero accounting system

It can move some text

Clunky perhaps, but if you do not find another way?
I have only ever used mailmerge with one document/label one record.

 

[AlliCarr]

Word (2019) will open pdf files, but convert them to editable format?
This is a report from Xero accounting system

View attachment 115269
It can move some text
View attachment 115270

Clunky perhaps, but if you do not find another way?
I have only ever used mailmerge with one document/label one record.

Thanks @Gasman that would definitely be an option. Although, I don't think most of my users will be able to follow such a process and get it right consistently! I think it could be a few too many steps for them.

 

[Gasman]

Well you could in Access automate opening the file in Word, setting that password in Word?, then save the file, all using VBA?

 

[ebs17]

@AlliCarr
Thinking about a modified mail merge and how to fill it out only makes sense if you have an idea of what it should look like in the end.
It's nice when @Gasman shows something visual. But something like that should actually come from you, since it's about your task. I derive methods from the objective and its fulfillment. Which methods I like the most at the moment is of secondary importance for now.

For a mail merge (original idea) you need a data source with all the information in one row. This is the case for the information in just one table. If you use a second dependent table, you have to convert the information from the additional rows into additional columns. This is done using the cross tab query mentioned above.

How many and which fields are involved specifically? The devil is in the details.
For one field you could solve this like this:

E

Post in thread 'Convert the query contents'

Jul 20, 2024

Cross tab query cannot achieve this effect.

If I use it, then yes.

TRANSFORM 
   MIN(X.SHID) AS xy 
SELECT 
   X.m_box_id 
FROM 
   (
      SELECT 
         T.m_box_id, 
         T.SHID, 
         "F" & DCOUNT("*", "tblNaobao", "m_box_id = '" & T.m_box_id & "' AND SHID < '" & T.SHID & "'") + 1 AS PivotColumn 
      FROM 
         tblNaobao AS T 
   ) AS X 
GROUP BY 
   X.m_box_id 
PIVOT 
   X.PivotColumn

• ebs17

• 

would this work with Albert Kallal's Super Easy Word Merge

I am not aware of this solution.