Results 1 to 13 of 13
  1. #1
    jhollingsh615 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    30

    Mail Merge AC2007 DB(many-to-many) with MSWord

    Mail Merge from AC2007 DB with many-to-many relationship. The DB is split front-end, back-end on a shared file server.



    My database contains the following 3 tables:
    tblContacts(individual people with contact info)
    tblFacilities(individual companies with contact info and corporate data)
    tblFacilityContacts(association table necessary because tblContacts and tblFacilities have a many-to-many relationship.

    I need to generate unique form letters(via email attachments) which can be addressed to the various facilities. Each letter would include the basic corporate data for the individual facility, as well as the multiple contacts related to that facility.

    The tables work great when I set up master/child relationships in forms. But, I'm not sure how to repeat such relationships in a query so that i can generate comparable form letters. Am I screwed?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Use the query designer to bring the tables into the design frame and join them. If you have Relationships set up the joins might automate. Need to make sure jointypes are correct. Probably should be 'All records from tblFacilityContacts ...' (a left or right, not sure which). Access Help has guidelines on using the designers and wizards.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jhollingsh615 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    30

    join properties

    June7,
    I appreciate the response. But, I have the tables in the design window. I think my problem is how to handle joins in a query where the base tables are related via a many-to-many relationship. In forms, AC automatically groups child data with the appropriate parent data based on the relationship.

    Is it possible to do the same thing in a query on two tables joined by an association table?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Yes, that is what I describe in my first post. The relating is through the 'junction' or association table. All 3 tables must be in the query. Jointype as I described. Right join I think.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    jhollingsh615 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    30
    The closest I have been able to come is
    CompanyA.....Contact1
    CompanyA.....Contact2
    CompanyA.....Contact3
    CompanyB.....Contact1
    CompanyB.....Contact2
    CompanyB.....Contact3
    and so on.....

    But, I need a query I can do a merge with that would allow me to insert data into the data document so that:

    The first form letter to contain......CompanyA + Contact1 + Contact2+Contact3
    The second form letter to contain CompanyB + Contcat1 + Contact2 + Contact3
    and so on.....

    What am I missing here?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    The query appears to be working correctly. Show the complete SQL statement for analysis.

    Now you want to concatenate all email contacts for a company into single addressees string for the To address line in the email? This will require VBA code. Check out http://allenbrowne.com/func-concat.html

    Review this recent thread https://www.accessforums.net/access/...ect-17713.html

    Why use Word? An Access report could probably serve as your form letter document.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    jhollingsh615 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    30

    Word Mail Merge v.s. Report

    There is actually more going on than just emailing the contacts. In the past, There was a single table and it included many rows of corporate data that was collected annually. The same table also included fields for the CEO, the first point of contact(POC), the second POC and the third POC and their various phones, emails, etc....

    Once each year, I would send a unique form letter via MS Word mail merge with attachment to the first POC. That attachment would have current corporate data, as well as all of the various contact info for each of the 4 contacts. In one table, it was easy to do, but it was not normalized and there was mega duplication. Last year I merged this corporate table with the office's main contact database which is people only. Now, we have a split frontend/backend database that is shared with 20 people. The core of that database is tblContacts which has only the people information and tblCompanies which has all the corporate data. These two main tables are related through an association table. The data is easily viewable through various parent/child forms.

    My problem now is how to send this year's annual email to POC1. The attachment sent with the email would include lots of unique corporate data, as well as the miscellaneous phone #s and email for all of the POCs for that one company. This is just a summary of what we currently have on file so that the POC1 for each facility can update the info and return to us.

    Each company's POC1 would only see the data for their individual facility. I did not think that an access Report could be used to do this....????

  8. #8
    jhollingsh615 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    30

    The sql is below. It appears to show the results I need...

    even though the POC info is spread across multiple lines in the resulting query. Also, the tblContacts file has contacts that are unrelated to this project. The ContactPriority2 field was how I dealt with the issue. ContactPriority2=9 is filtered out because those people are related to the referenced company, but are not involved with this project. Be gentle here please...I'm feeling very overwhelmed...

    SELECT tblJapanCompanyDatabase.[JapanCompanyID#], tblJapanCompanyDatabase.UPDATE, tblJapanCompanyDatabase.CONAME1, tblJapanCompanyDatabase.ADDRESS1, tblJapanCompanyDatabase.CITY, tblJapanCompanyDatabase.STATE, tblJapanCompanyDatabase.ST, tblJapanCompanyDatabase.ZIP, tblJapanCompanyDatabase.MainPhone, tblJapanCompanyDatabase.PhysAdd1, tblJapanCompanyDatabase.PhysAdd2, tblJapanCompanyDatabase.PhysCity, tblJapanCompanyDatabase.PhysState, tblJapanCompanyDatabase.PhysZip, tblJapanCompanyDatabase.ParentCos, tblJapanCompanyDatabase.BusinessSummary, tblJapanCompanyDatabase.JapanEmp2010, tblJapanCompanyDatabase.LocalEmp2010, tblJapanCompanyDatabase.TotalEmp2010, tblJapanCompanyDatabase.Function2010, tblJapanCompanyDatabase.Sector2010, tblJapanCompanyDatabase.FacType2010, tblJapanCompanyDatabase.[Responded 2010], tblJapanCompanyDatabase.[10%Japanese Owned:], tblJapanCompanyDatabase.[100%JapaneseOwned], tblContacts.ContactID, tblContacts.[Active/Inactive], tblContacts.Salute, tblContacts.First, tblContacts.init, tblContacts.Last, tblContacts.KanjiName, tblContacts.[Company/Organization], tblContacts.title, tblContacts.address, tblContacts.Primarycity, tblContacts.Primaryst, tblContacts.Primaryzip, tblContacts.PrimaryPhone, tblContacts.DirectPhone, tblContacts.PrimaryCel, tblContacts.PrimaryFAX, tblContacts.PrimaryEmail, tblContactsCompanies.ContactPriority2
    FROM tblContacts RIGHT JOIN (tblJapanCompanyDatabase LEFT JOIN tblContactsCompanies ON tblJapanCompanyDatabase.[JapanCompanyID#] = tblContactsCompanies.CompanyID) ON tblContacts.ContactID = tblContactsCompanies.ContactID
    GROUP BY tblJapanCompanyDatabase.[JapanCompanyID#], tblJapanCompanyDatabase.UPDATE, tblJapanCompanyDatabase.CONAME1, tblJapanCompanyDatabase.ADDRESS1, tblJapanCompanyDatabase.CITY, tblJapanCompanyDatabase.STATE, tblJapanCompanyDatabase.ST, tblJapanCompanyDatabase.ZIP, tblJapanCompanyDatabase.MainPhone, tblJapanCompanyDatabase.PhysAdd1, tblJapanCompanyDatabase.PhysAdd2, tblJapanCompanyDatabase.PhysCity, tblJapanCompanyDatabase.PhysState, tblJapanCompanyDatabase.PhysZip, tblJapanCompanyDatabase.ParentCos, tblJapanCompanyDatabase.BusinessSummary, tblJapanCompanyDatabase.JapanEmp2010, tblJapanCompanyDatabase.LocalEmp2010, tblJapanCompanyDatabase.TotalEmp2010, tblJapanCompanyDatabase.Function2010, tblJapanCompanyDatabase.Sector2010, tblJapanCompanyDatabase.FacType2010, tblJapanCompanyDatabase.[Responded 2010], tblJapanCompanyDatabase.[10%Japanese Owned:], tblJapanCompanyDatabase.[100%JapaneseOwned], tblContacts.ContactID, tblContacts.[Active/Inactive], tblContacts.Salute, tblContacts.First, tblContacts.init, tblContacts.Last, tblContacts.KanjiName, tblContacts.[Company/Organization], tblContacts.title, tblContacts.address, tblContacts.Primarycity, tblContacts.Primaryst, tblContacts.Primaryzip, tblContacts.PrimaryPhone, tblContacts.DirectPhone, tblContacts.PrimaryCel, tblContacts.PrimaryFAX, tblContacts.PrimaryEmail, tblContactsCompanies.ContactPriority2
    HAVING (((tblJapanCompanyDatabase.[10%Japanese Owned:])=Yes) AND ((tblContacts.[Active/Inactive])="active") AND ((tblContactsCompanies.ContactPriority2)<>9));

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Query could look a little simpler if you used wildcard, especially if you retrieve virtually every field from the table. I notice special characters in names - not advised. JapanComanyID# would be better as JapanCompanyIDNum and 100%JapaneseOwned better as 100PctJapaneseOwned.

    SELECT tblJapanCompanyDatabase.*, tblContacts.*, tblContactsCompanies.ContactPriority2 FROM ...

    Not sure why you are grouping in the query. Would think report Grouping and Sorting features could accomplish each POC seeing only their own data. But would need to know more about your data structure.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    jhollingsh615 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    30
    June7,
    I will take your advice about the special characters in name fields later. Can't tackle that one just yet, but I appreciate the note. Guess I should have realized sooner.

    This is my first attempt with a report, primarily because I have always used mailmerges to email before. I am only trying a report now because it was suggested as an alternative to the mail merge, which I could not get to work with the query built from the many-to-many relationship of all 3 tables. I still don't understand how to get an individual company's page from the full report to generate to a separate emails to each POC1. But.....eventually, something will click I guess......I appreciate you taking time to look.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Sorry, my suggestion for Grouping and Sorting was erroneous for this scenario. The G&S could separate POC data to separate pages of one report but the email procedure could not isolate which page to send. What might be required is the report is filtered to only one POC at a time and each is sent an individual email with report of their data attached. This can be done through VBA programming. The email procedure would loop through recordset of addresses and send individual email and attached report.

    Your earlier post seemed to indicate you wanted all the contacts for a company in the same email To address (multiple addressees for one email). This implied that all contacts for a company would get the same email and attachment. If this is not what you want, then the concatenation of address emails as one To string is not relevant.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    jhollingsh615 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    30
    It is likely my own ineptitude when it comes to explaining the project. Apologies for not being more clear. POC1 should receive all of the information for their company solely, in a single email attachment. They update all of their information on behalf of their colleagues.

    I know next to nothing about VBA and now fear that I may be forced into doing this manually this year. These must be sent early next week and I doubt I can learn enough VBA to accomplish the mail merge to email by then.....If you have any VBA resources to point me towards, it would be appreciated. Otherwise, thank you for attempting the assist.

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    So you want one email per company? Same concept applies. Loop through recordset, send email and attachment.

    Yes, I expect you need much more than a week to learn VBA well enough to do this yourself. The email link I provided is a sample. I have a few books about VB programming but none specific to VBA, find what I need on the web. This might be a reference you want to consider: Access™ 2007 Programming by Example with VBA, XML, and ASP by Julitta Korol, Wordware Publishing, Inc.

    Also need understanding of basic programming concepts.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Mail Merge
    By sakhtar in forum Access
    Replies: 8
    Last Post: 09-20-2020, 09:10 AM
  2. Mail merge problem
    By Affendi in forum Import/Export Data
    Replies: 6
    Last Post: 02-08-2011, 07:38 AM
  3. Automated Mail Merge
    By celinae in forum Programming
    Replies: 1
    Last Post: 09-20-2010, 09:57 AM
  4. Mail Merge
    By Nixx1401 in forum Access
    Replies: 1
    Last Post: 02-15-2010, 10:51 AM
  5. Mail merge
    By grgerhard in forum Forms
    Replies: 0
    Last Post: 04-25-2006, 05:06 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums