Mail Merge Many-to-One Relationships (1 Viewer)

wolves1

Registered User.
Local time
Yesterday, 21:10
Joined
Feb 3, 2019
Messages
22
Hello,

I'm rather new with creating mail merge letters. Requesting some help on creating a mail merge letter for one-to-many relationships. Replaced names for privacy for the scenario:

Data Headers:
1. Shop Name
2. Related Shop Name (2nd store, but different shop name, but under same owner of "Shop Name")
3. Address

Data Content:
1. Shop Name: Famous Donuts
2. Related Shop Name: Ice Cream, Unicorns, Biscuits
3. Shop Name and Related Shop Name have different address locations

I want to create a mail merge that can send "Famous Donuts" one letter thanking them for the long-term business, but the letter includes all of the related shops (Ice Cream, Unicorns, and Biscuits) instead of sending three (3) letters for the 3 different shops to the same person. Is it possible to create this without any external add-ons which I've seen online? Thanks in advance!
 

wolves1

Registered User.
Local time
Yesterday, 21:10
Joined
Feb 3, 2019
Messages
22
You kind of petered out on the Data Content portion:



So what's that look like?

The ConcatRelated function may be of use:


II'm not sure what do you mean by petering out on the data content portion. If specifically the address you're looking for, then for example, parent shop (Famous Donuts) have an address of 1234 hello world. While the related shop name (Ice Cream) could have an address of a PO Box 23648. On the mail merge letter, I would like it if it would group those shops together based on my data source and create only 1 letter to "Famous Donuts" that also includes "Ice Cream" listed as one of the shops as well. Hopefully that makes sense, I've created a table of how the data source Excel file would look.

Shop NameRelated Shop NameShop Name AddressRelated Shop Name Address
Famous DonutsIce Cream1234 hello worldPO Box 23648
Famous DonutsUnicorns1234 hello world645 Grene St.
Famous DonutsBiscuits1234 hello world6975 Dots
 
Last edited:

plog

Banishment Pending
Local time
Yesterday, 23:10
Joined
May 11, 2011
Messages
10,998
You showed me what data was to appear in section 1. You showed me what data was to appear in section 2. You didn't show me what data was to appear in section 3. You simply said they are different.

Using the data tell me what appears in the 3 sections of your letter. Don't explain it, show me.
 

wolves1

Registered User.
Local time
Yesterday, 21:10
Joined
Feb 3, 2019
Messages
22
Hello,

I have attached the sample data source excel file and an example of how the mail merge letter would look. Actual letter and data is different, but the idea is the same. If you look at the mail merge letter, I want to send one letter to the business recipient, "Famous Donuts", but in the letter content also list any associated shop names. In this example, it would be Ice Cream, Unicorns, and Biscuits.

I started with the basic mail merge field codes for the first related shop name, but i'm not sure how to add the rest of the related shops to the same mail merge without having to create it manually. I tried researching online, but I see a lot of people use external add-ons to create many-to-one relationship mail merges, but I don't want to use those. I've also seen using the NEXTIF commands, but would need some guidance on that. Hopefully this clears up what i'm needing help on.
 

Attachments

  • Business List Mail Merge.zip
    19.1 KB · Views: 460

plog

Banishment Pending
Local time
Yesterday, 23:10
Joined
May 11, 2011
Messages
10,998
Your document is asking for a data source and then other errors so its not working for me.

Seriously, my request isn't that difficult. Let's say the the data in your second post is what's in your database. What data should appear on your letter. You were so close in your first post. Just go back to it and replace #3 from an explanation to data.
 

wolves1

Registered User.
Local time
Yesterday, 21:10
Joined
Feb 3, 2019
Messages
22
Hello,

My apologies if my previous posts was confusing, but like referring to the first original post. The 3rd data for address, is the physical address of the shop.

For Example:

Letter Addressed to:
Famous Donuts
1234 hello world
New York, NY 65895


Letter Content lists any associated shop name to the same owner of Famous Donuts:
Ice Cream
PO Box 23648
Seattle, WA 66589


then after "Ice Cream", is there a way to include "Unicorns" and "Biscuits" shop to the same letter, so instead of sending 3 separate letters, it sends only 1 letter to Famous Donuts which includes the rest of the associated shops.
 

wolves1

Registered User.
Local time
Yesterday, 21:10
Joined
Feb 3, 2019
Messages
22
I see, so even the use of NEXTIF commands won't really work? I saw something similar online, but it was adding products not different shops with addresses.
 

plog

Banishment Pending
Local time
Yesterday, 23:10
Joined
May 11, 2011
Messages
10,998
I'd never heard of NEXTIF so it might be feasible. The real trick needed is getting the prior row's value which is very difficult and hacky in Access.

Could you export your data to Excel then follow the instructions you found for NEXTIF?

Why won't an Access Report work?
 

wolves1

Registered User.
Local time
Yesterday, 21:10
Joined
Feb 3, 2019
Messages
22
I tried to follow the NEXTIF command video I saw on YouTube. Not sure what version of Microsoft they could be using, so it seems like the version I have doesn't support it. Also, it looks pretty complicated as it's setting specific ID's to the data. This was the video I saw on YouTube for using NEXTIF commands. I'm not if this has any relation to what i'm trying to accomplish:

An Access Report will work, but I was also curious if there was a possibility of other ways to do it. I guess not.
 

Users who are viewing this thread

Top Bottom