Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-02-2015, 10:30 PM
AndyS AndyS is offline I want to create a lists within lists (nested merges) Mac OS X I want to create a lists within lists (nested merges) Office for Mac 2011
Novice
I want to create a lists within lists (nested merges)
 
Join Date: Dec 2015
Posts: 6
AndyS is on a distinguished road
Default I want to create a lists within lists (nested merges)

I have Microsoft office for Mac 14.5.8 running on OS X 10.11.1.



I have a basic level of VBA programming skills.

My requirement is to generate a WORD document cataloguing a number of artworks with, for each work, a page containing details of the work and a list of the exhibitions it has been in. The first bit I can do with mailmerge, but I don’t know how to make the embedded list of exhibitions for each work. I want to create a line for each exhibition, so it is like a mailmerge-within-a-mailmerge. Is this possible with VBA? (I don’t necessarily have to use mailmerge if there is a better way).

I currently have an EXCEL workbook functioning as a database for the artworks with a table on each worksheet (tab). This could be migrated to SQLLite or similar if that helps. Exhibitions can tour, so location is a separate table; an exhibition will take place at one or more locations, so ideally I want to list the locations for each exhibition as well, giving two levels of embedding.

Tables:
  • Object: entry per artwork with fields like: artist, title, materials and measurements; keyed with an object_id.
  • Exhibition: name, catalogue reference; keyed with a short name (acronym) as exhibition_id
  • Location: name of a location at which exhibitions are held; keyed with a short name (acronym) as location_id.
  • Object-Exhibition: object_id, exhibition_id, catalogue number. Indicates which works appeared at each exhibition.
  • Exhibition-location: exhibition_id, location_id, start date, end date. Indicates which exhibitions were held at each location.
If anyone can give me some sample code, and/or point me to where I can find instructions, I would be most grateful!
Reply With Quote
  #2  
Old 12-03-2015, 12:44 AM
macropod's Avatar
macropod macropod is offline I want to create a lists within lists (nested merges) Windows 7 64bit I want to create a lists within lists (nested merges) Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Are there multiple records per artwork, or are the exhibitions all part of the same record? In Excel 1 row = 1 record.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 12-03-2015, 09:18 PM
AndyS AndyS is offline I want to create a lists within lists (nested merges) Mac OS X I want to create a lists within lists (nested merges) Office for Mac 2011
Novice
I want to create a lists within lists (nested merges)
 
Join Date: Dec 2015
Posts: 6
AndyS is on a distinguished road
Default

The tables are designed along relational lines...
  • Object has one record (row, entry) per object (artwork)
  • Exhibition has one record per exhibition
  • Location has one record per location
  • Object-Exhibition has one record for each object in each exhibition
  • Exhibition-location has one record for each location for each exhibition

I do not need a complete solution; mainly I need an indication of how one can merge a sub-list within a list.
Reply With Quote
  #4  
Old 12-03-2015, 11:24 PM
macropod's Avatar
macropod macropod is offline I want to create a lists within lists (nested merges) Windows 7 64bit I want to create a lists within lists (nested merges) Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

OK, a mailmerge can only reference a single data source (in this case, a single worksheet). That said, if that worksheet provides keys to the others, you could combine the mailmerge with a DATABASE field to retrieve data from the other sheets and output the data in a table format. Using a DATABASE field requires a working knowledge of SQL.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 12-08-2015, 10:32 PM
AndyS AndyS is offline I want to create a lists within lists (nested merges) Mac OS X I want to create a lists within lists (nested merges) Office for Mac 2011
Novice
I want to create a lists within lists (nested merges)
 
Join Date: Dec 2015
Posts: 6
AndyS is on a distinguished road
Default

Mmm. Thanks for confirming my understanding so far.

I can handle SQL, but I am not sure what you mean by a DATABASE field, or how it will help me? Do you mean the Get external data from a database feature? If so, then I also need to instal an ODBC driver and have not yet found a compatible one.

I have an idea that might move me forward, but still insufficient detail to implement. I could:
  1. Create a (non-relational) Object-Exhibitions table with Object_id in the first column, repeated for each exhibition that applies to that object, followed by fields with the exhibition details. (Let's suppose I can get the list of locations in there somehow).
  2. Run a Mailmerge on that table in such a way that a separate little WORD document is created each time the Object_id changes; so each of these mini-documents contains the formatted list of exhibitions for one object, and is named with the Object_id.
  3. Run a second "Master" Mailmerge on the Object table (if necessary in a separate workbook) that picks up all the basic object data fields (Name, Materials, Size etc) and includes for each Object the corresponding Exhibitions WORD (sub-) document, identified by the Object_id, that was produced by the previous Mailmerge.
The only problem is I do not know exactly how to do step 2; though I read somewhere that it should be possible. What do you think?
Reply With Quote
  #6  
Old 12-09-2015, 12:07 AM
macropod's Avatar
macropod macropod is offline I want to create a lists within lists (nested merges) Windows 7 64bit I want to create a lists within lists (nested merges) Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Word has numerous types of field, one of which is the DATABASE field. For a comprehensive list, go to Insert|Quick Parts|Field.

For a non-mailmerge implementation of a DATABASE field, see: https://www.msofficeforums.com/mail-...html#post67110
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 12-09-2015, 09:39 PM
AndyS AndyS is offline I want to create a lists within lists (nested merges) Mac OS X I want to create a lists within lists (nested merges) Office for Mac 2011
Novice
I want to create a lists within lists (nested merges)
 
Join Date: Dec 2015
Posts: 6
AndyS is on a distinguished road
Default

Ah! I was confused, I thought you were talking about a 'DATABASE' field in EXCEL!!

Yes, in WORD for Mac there are no 'Quick parts' but one can insert a DATABASE field with Insert -> Field -> Mailmerge -> Database.

I guess you are suggesting I create the Exhibitions table to look like a database (even in EXCEL?), then from the Mailmerge master document use a DATABASE field with an SQL query to generate the list of exhibitions for each Object? Can I use Object_id , the key to the record in the Mailmerge Object table, as a parameter in the query?

The SQL query would then do "Get me a table of all the exhibitions belonging to this object"?
I see there are some table formatting options in the field, but it is going to take some experimentation to get it right.

This does look like it might be a bit complicated for my relatively small application (119 records). What do you think of my suggestion in the previous post, to use intermediate mini-documents, themselves created by a mail merge with a bit of VBA to control the loop that creates an Exhibitions mini-document for each Object? I think this might give me more flexibility on formatting?
Reply With Quote
  #8  
Old 12-09-2015, 10:09 PM
macropod's Avatar
macropod macropod is offline I want to create a lists within lists (nested merges) Windows 7 64bit I want to create a lists within lists (nested merges) Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

OK, so you have -

Quote:
Originally Posted by AndyS View Post
Tables:
  • Object: entry per artwork with fields like: artist, title, materials and measurements; keyed with an object_id.
  • Exhibition: name, catalogue reference; keyed with a short name (acronym) as exhibition_id
  • Location: name of a location at which exhibitions are held; keyed with a short name (acronym) as location_id.
  • Object-Exhibition: object_id, exhibition_id, catalogue number. Indicates which works appeared at each exhibition.
  • Exhibition-location: exhibition_id, location_id, start date, end date. Indicates which exhibitions were held at each location.
Your mailmerge would be keyed to, say, the Object table. That gives you access to the object_id, which you can use in the DATABASE field to query the Object-Exhibition table and, using the exhibition_id there, to query the Exhibition table and the Exhibition-location table and, via the location_id there, the Location table.

Although the DATABASE field outputs its results as a table, you could reformat the table or throw it away (just keeping the data) afterwards. And, depending on your requirements, you could have multiple DATABASE fields, with their own outputs for each record returned from the Object table.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #9  
Old 12-10-2015, 10:28 PM
AndyS AndyS is offline I want to create a lists within lists (nested merges) Mac OS X I want to create a lists within lists (nested merges) Office for Mac 2011
Novice
I want to create a lists within lists (nested merges)
 
Join Date: Dec 2015
Posts: 6
AndyS is on a distinguished road
Default

Paul,

This is starting to look do-able, though I still need some detail, so I am going to present a simplified example in the next post.

First, here, the story so far…

I followed the link you provided in a previous post, which gives a very helpful example of the use of a DATABASE field. In that example, three database queries are executed and each allocated to a table, but it only produces one letter. I need to combine the results of the query with a mailmerge.

The Microsoft article on ‘Catalog’ mail merging seems to make that connection, but WORD does not have multiple levels built in (to nest Objects, Exhibitions and Locations).

I then found your posting on “many to one” cataloging. The examples look close to what I want to achieve, but when I installed the .dotm file on my system (WORD 2011 for Mac), it did not work. I got nothing in the toolbar and WORD continued working a one level, i.e. made a whole new record for each Exhibition and Location. Is the plugin compatible with WORD for Mac? There is a Zotero.dotm in the same folder and that works OK.

From these and other postings, I see that things become clearer with an example, so I have created one in the next post: the Catalogue Mailmerge Challenge.

Andy
Reply With Quote
  #10  
Old 12-10-2015, 10:45 PM
AndyS AndyS is offline I want to create a lists within lists (nested merges) Mac OS X I want to create a lists within lists (nested merges) Office for Mac 2011
Novice
I want to create a lists within lists (nested merges)
 
Join Date: Dec 2015
Posts: 6
AndyS is on a distinguished road
Default Andy’s catalogue mailmerge challenge

The attached EXCEL table (Sheet1) is a “flattened” version of a relational database. This would normally be generated as a query from the actual database, which is currently an EXCEL workbook, but this one was made by hand for test purposes to avoid being distracted by the details of the database.

I want to generate a WORD document with a section (new page) for each Object. (An Object is a work of art, drawing, painting, sculpture etc). At the top of the section is the fixed singular data for the object: Artist, Title, Year, Dimensions and Materials.

Then there is a section headed Exhibitions, which lists all the Exhibitions (if any) in which the Object has appeared, in chronological order of (first) Start date, giving the name of the Exhibition and the Catalogue_nr of this Object at that Exhibition. If the Object has never been exhibited then the text “No exhibition record” should be entered instead.

For each Exhibition there is a list of Locations with Start and End dates, in chronological order of Start date. There will often only be one Location, but touring exhibitions can have several. The first one should appear on the same line as the Exhibition details, and others on subsequent lines with blanks or ditto marks for the Exhibition details.

The attached WORD file is a start, but not complete.

The rules are:
  • Object_id is a unique identifier (key) for Object. Artist, Year, Title, Dimensions and Materials are attributes of Object. These are not unique over the table as a whole (distinct Objects could be called “Untitled” and have the same Date, Materials and Dimensions).
  • An Object may appear in zero or more Exhibitions. Exhibition_id is a unique identifier for Exhibition, in case two distinct exhibitions have the same name.
  • More than one of our Objects may appear in the same Exhibition. They each have a Catalogue_nr, which is unique for the Object at that Exhibition (there is no requirement to check this).
  • An Exhibition takes place at one or more Locations (there should be an error message if there is no Location data). Location_id is a unique identifier for the Location, in case the names are the same (though that is unlikely). The Start and End dates define the relationship between Exhibition and Location. (Dates would not overlap, but there is no requirement to check this).

Who can rise to this challenge and show me how to make this simple example work on my computer, with Mac OS X 10.11.1 and Microsoft Office for Mac 2011 version 14.5.8?
Attached Files
File Type: xlsx Catalogue mailmerge test data.xlsx (86.6 KB, 16 views)
File Type: doc Catalogue mailmerge test master.doc (33.5 KB, 16 views)
Reply With Quote
  #11  
Old 12-11-2015, 03:05 PM
macropod's Avatar
macropod macropod is offline I want to create a lists within lists (nested merges) Windows 7 64bit I want to create a lists within lists (nested merges) Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

A directory/catalogue mailmerge working with your flattened workbook would be possible but, as is evident in the tutorial, adding extra subgroup keys increases the complexity. For two keys (e.g. Object_id and Exhibition_id), the examples in the tutorial would already suffice. For some worked examples, see the attachments to the posts at:
https://www.msofficeforums.com/mail-...html#post23345
https://www.msofficeforums.com/mail-...html#post30327
http://windowssecrets.com/forums/sho...l=1#post928391

Alternatively, you may want to try one of the Many-to-One Mail Merge add-ins, from:
Graham Mayor at http://www.gmayor.com/ManyToOne.htm; or
Doug Robbins at http://bit.ly/1hduSCB

That said, the advantages of the DATABASE field include not having to flatten the workbook structure and automatically generating a table for the output. On its own, a directory/catalogue mailmerge doesn't work with multiple sheets and can't directly produce a single table for each group or subgroup.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Tags
list within list, nested merge, vba mail merge

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
I want to create a lists within lists (nested merges) Cant create any more numbered lists. rapid3642 Word 9 11-14-2014 08:28 PM
I want to create a lists within lists (nested merges) Need Serious Help with Outlines/Lists Stone Word 5 09-11-2012 04:56 PM
Numbered Lists jburtis Word 1 04-07-2012 02:42 PM
Distribution Lists WildBill Outlook 2 08-11-2011 08:15 AM
I want to create a lists within lists (nested merges) Searching dates to create lists brianh Excel 2 01-18-2010 01:58 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:29 PM.


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