Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-22-2021, 09:58 AM
dlconnolly@mac.com dlconnolly@mac.com is offline Using Merge Code to list question responses in multiple reports Windows XP Using Merge Code to list question responses in multiple reports Office 2019
Novice
Using Merge Code to list question responses in multiple reports
 
Join Date: Apr 2021
Posts: 4
dlconnolly@mac.com is on a distinguished road
Default Using Merge Code to list question responses in multiple reports

I have a spreadsheet created from end of course surveys and am trying to merge the responses into reports for each instructor, grouped by the courses.

I am basing this on instruction from Mail Merge with Grouping.



My test with the first question in the survey worked fine. But, we have 20 questions and the collective answers that need to be in each report. When I tried to add the second question, that's when it got wonky.

The first attempt produced reports with
Question_1: the question written out once and all of the answers listed below
Question_2: the question written out with every answer
This is just for situational awareness since the code wasn't saved when I tried to fix it.

I tried to fix it with the code seen in MergeQuestion_code_example.jpg
The results of this code can be seen in MergeQuestion_merge_example.jpg
The results didn't even include Question_2 or it's values.

A shot of the table I am working with is MergeQuestion_table_example.jpg

I realise this method may require met to type a ton of code using the fn/F9 method. That's okay for now.
Reply With Quote
  #2  
Old 04-22-2021, 02:07 PM
macropod's Avatar
macropod macropod is offline Using Merge Code to list question responses in multiple reports Windows 10 Using Merge Code to list question responses in multiple reports Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,100
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

When producing multiple tables, it would be better to use a series of DATABASE fields in a normal ‘letter’ mailmerge main document and a macro to drive the process. An outline of this approach can be found at:
Many to one email merge using tables - Microsoft Community

If you're using a relational database or, Excel workbook with a separate table with just a single instance of each of the grouping criteria (in the case the instructor name/id), a DATABASE field in a normal ‘letter’ mailmerge main document could be used without the need for a macro. An outline of this approach can be found at:
Mail Merge - To a Word Table on a Single Page - Microsoft Community
For some working examples, see:
https://www.msofficeforums.com/mail-...-multiple.html
https://www.msofficeforums.com/mail-...tml#post151706
Merge excel list into Word Receipt
(the second of these uses a macro to apply some additional formatting - which may be pertinent in this case).

The DATABASE field can even be used without recourse to a mailmerge. An example of such usage can be found at: https://www.msofficeforums.com/mail-...html#post67097
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 04-27-2021, 05:27 AM
dlconnolly@mac.com dlconnolly@mac.com is offline Using Merge Code to list question responses in multiple reports Windows XP Using Merge Code to list question responses in multiple reports Office 2019
Novice
Using Merge Code to list question responses in multiple reports
 
Join Date: Apr 2021
Posts: 4
dlconnolly@mac.com is on a distinguished road
Default

Thank you for the examples above, but they are what I already referred to originally to get to the point of where I am now.
Reply With Quote
  #4  
Old 04-27-2021, 05:55 AM
macropod's Avatar
macropod macropod is offline Using Merge Code to list question responses in multiple reports Windows 10 Using Merge Code to list question responses in multiple reports Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,100
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

Well, given that you haven't provided any details about your document or the data, that's about as much help as it's possible to give at this stage. Telling us the names of a number of images you've made but which we can't see isn't much help either.

Even your reference to «instruction from Mail Merge with Grouping» is so obscure it's impossible to know what you were referring to.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 04-27-2021, 05:57 AM
dlconnolly@mac.com dlconnolly@mac.com is offline Using Merge Code to list question responses in multiple reports Windows XP Using Merge Code to list question responses in multiple reports Office 2019
Novice
Using Merge Code to list question responses in multiple reports
 
Join Date: Apr 2021
Posts: 4
dlconnolly@mac.com is on a distinguished road
Default

Apologies, I had attached screen shots.

Here are the attachments, hoping they upload correctly this time.
Attached Images
File Type: jpg MergeQuestions_code_example.JPG (90.5 KB, 7 views)
File Type: jpg MergeQuestions_merged_example.JPG (86.9 KB, 7 views)
File Type: jpg MergeQuestions_table_example.jpg (107.8 KB, 7 views)
Reply With Quote
  #6  
Old 04-27-2021, 02:47 PM
macropod's Avatar
macropod macropod is offline Using Merge Code to list question responses in multiple reports Windows 10 Using Merge Code to list question responses in multiple reports Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,100
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

This would be quite simple using the DATABASE field approach, for which all you need in addition to the worksheet sheet containing the answers is a sheet containing just the course IDs. Once it's configured that way, simply insert as many DATABASE fields as you have questions to report on. The basic DATABASE field would look something like:

{DATABASE \d "{FILENAME \p}/../Survey Data.xlsx" \s " SELECT [Question_1] FROM [Sheet1$] WHERE [Course_ID] = {MERGEFIELD Course_ID}" \l "15" \b "49" \h}

Since you haven't told us anything about the actual workbook & worksheet names, I'm unable to use those. The workbook name would replace 'Survey Data' and the worksheet name would replace 'Sheet1'.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 04-29-2021, 09:14 AM
dlconnolly@mac.com dlconnolly@mac.com is offline Using Merge Code to list question responses in multiple reports Windows XP Using Merge Code to list question responses in multiple reports Office 2019
Novice
Using Merge Code to list question responses in multiple reports
 
Join Date: Apr 2021
Posts: 4
dlconnolly@mac.com is on a distinguished road
Default Files attached

In order to share the actual files, I had to get permission, then delete a lot of the text due to sensitive topics.

Attached are the two files I am working from, a sample excel file and the word document.

In case the toggle code feature doesn't work, I included a screen grab of the updated code that I tried.

I tried the DATABASE method but as a novice, editing the code was giving me errors.
Attached Images
File Type: jpg MergeQuestions_code_example.JPG (80.5 KB, 5 views)
Attached Files
File Type: docx Merge Report TEMPLATE.docx (16.0 KB, 1 views)
File Type: xlsx Survey_Responses_Sample.xlsx (10.3 KB, 1 views)
Reply With Quote
  #8  
Old 04-29-2021, 02:59 PM
macropod's Avatar
macropod macropod is offline Using Merge Code to list question responses in multiple reports Windows 10 Using Merge Code to list question responses in multiple reports Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,100
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

See attached. When you start the mailmerge, connect it to the 'Course IDs' Worksheet.

Note: As coded, the Word Document and Excel Workbook need to be kept in the one folder.

The formats of the output tables are controlled by the \l and \b switches in the DATABASE fields. The parameters for the:
• \l switch are determined via Insert|QuickParts>Field: Database>GetData: SelectFile&Table>Table AutoFormat>Apply Special Formats to
• \b switch are defined at: Field codes: Database field - Office Support and can also be determined via Insert|QuickParts>Field: Database>GetData: SelectFile&Table>Table AutoFormat>Formats to Apply
Attached Files
File Type: docx Merge Report.docx (14.4 KB, 1 views)
File Type: xlsx Survey Responses.xlsx (11.2 KB, 6 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA code to CC multiple people in mail merge by word kuankailok Mail Merge 3 05-04-2020 02:08 PM
Using Merge Code to list question responses in multiple reports How to make MATCH/INDEX formula return multiple successive responses. danigirl121 Excel 7 06-08-2016 01:57 PM
Task List Reports dbsoccer Outlook 1 04-06-2013 06:33 AM
Using Merge Code to list question responses in multiple reports Merge Reports... DMA-Pacific Word 1 03-27-2012 11:49 PM
Sending multiple responses to an individual ladeda16 Outlook 0 10-06-2009 03:18 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 04:30 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