Results 1 to 13 of 13
  1. #1
    Join Date
    May 2021
    Posts
    5

    Mail Merge Issue with Lookup Fields

    Hi everyone, first time poster so please be kind.

    I have a database of students who are competing at a Zone Athletics Carnival. The database has three tables Athletes, Events and Gender. The Events table has a list of events. In the Athletes table I have created a Lookup field that allows me to select the events the students are competing in in the Event 1, Event 2 etc. fields. I have done the same with Gender. The reason is so I dont have to type in Long Jump (for example) every time, I just type L and it fills it out automatically. Saves a lot of time.

    The problem is when I go to Mail Merge into Word, instead of having the event name, it only has a number which is the ID number of the event in the Events table. Why is it doing this, and how can I get it to have the words "Long Jump" instead of the ID number?

    Database attached for reference.

    Thanks!Crookwell HS Athletics.zip

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    It is doing that because the actual value saved is the ID. If you want the text descriptor, build a query that joins tables and use that query for the mail merge.

    Why use Word - so you can do some elaborate text formatting?
    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
    Join Date
    May 2021
    Posts
    5
    Thanks for that. I use Word so I can create a permission note to attend the carnival. The note has the student's name, year group, age group and events they are competing in. That way the students know exactly which events they are involved in, and I know which students to chase who haven't collected their notes.

  4. #4
    Join Date
    May 2021
    Posts
    5
    Any chance I could get a hand with the Query?

  5. #5
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    See it this works. You may need to add more fields for the mail merge.
    Code:
    SELECT Athletes.Surname, Athletes.[First Name], Gender.Gender, Athletes.[Event 1], Athletes.[Event 2], Athletes.[Event 3], Athletes.[Event 4], Athletes.[Event 5], Athletes.[Event 6], Athletes.[Event 7], Athletes.[Event 8], Athletes.[Event 9], Athletes.[Event 10]
    FROM Gender INNER JOIN (Events RIGHT JOIN Athletes ON (Events.ID = Athletes.[Event 10]) AND (Events.ID = Athletes.[Event 9]) AND (Events.ID = Athletes.[Event 8]) AND (Events.ID = Athletes.[Event 7]) AND (Events.ID = Athletes.[Event 6]) AND (Events.ID = Athletes.[Event 5]) AND (Events.ID = Athletes.[Event 4]) AND (Events.ID = Athletes.[Event 3]) AND (Events.ID = Athletes.[Event 2]) AND (Events.ID = Athletes.[Event 1])) ON Gender.ID = Athletes.Gender;
    Edit: Here's same code run thru a formatter:

    Code:
    SELECT athletes.surname,
           athletes.[first name],
           gender.gender,
           athletes.[event 1],
           athletes.[event 2],
           athletes.[event 3],
           athletes.[event 4],
           athletes.[event 5],
           athletes.[event 6],
           athletes.[event 7],
           athletes.[event 8],
           athletes.[event 9],
           athletes.[event 10]
    FROM   gender
           INNER JOIN (events
                       RIGHT JOIN athletes
                               ON ( events.id = athletes.[event 10] )
                                  AND ( events.id = athletes.[event 9] )
                                  AND ( events.id = athletes.[event 8] )
                                  AND ( events.id = athletes.[event 7] )
                                  AND ( events.id = athletes.[event 6] )
                                  AND ( events.id = athletes.[event 5] )
                                  AND ( events.id = athletes.[event 4] )
                                  AND ( events.id = athletes.[event 3] )
                                  AND ( events.id = athletes.[event 2] )
                                  AND ( events.id = athletes.[event 1] ))
                   ON gender.id = athletes.gender;

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    So why involve Word and not just build Access report?

    Data structure is not normalized.
    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
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Quote Originally Posted by June7 View Post
    So why involve Word and not just build Access report?

    Data structure is not normalized.
    I agree that if the OP wants to use Word mail-merge, Excel would probably be better than Access for the data format exhibited, which is decidedly spreadsheet, not database.
    With the current format, the only reason to use Access would be to eliminate the need for Word.
    Either way, it looks like a simple one-off to get a quick and dirty job done.

  8. #8
    Join Date
    May 2021
    Posts
    5
    Quote Originally Posted by June7 View Post
    So why involve Word and not just build Access report?

    Data structure is not normalized.
    Because I know how to create a Mail Merge letter in Word using an Access database. I don't know how to build an Access report.

    One of the saddest and most common threads I tend to see on these types of forum is that everyone is clearly an expert and expects everyone else to be an expert as well. I have some basic knowledge of Access and creating Mail Merge documents in Word so that is the way I chose to do it. All I asked for was some assistance with my problem and I get met with "elitist" comments like yours. If I knew how to build an Access report and complete my task the way you are suggesting, I wouldn't be on here asking for assistance in the first place.

    You aren't making the environment for newbies particularly welcoming.

  9. #9
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Post#5 didn't meet your needs? I tried so hard.

  10. #10
    Join Date
    May 2021
    Posts
    5
    Quote Originally Posted by davegri View Post
    Post#5 didn't meet your needs? I tried so hard.
    I appreciate your efforts, thanks.

  11. #11
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Not really, they are just trying to stop you going down a path that is just going to get more difficult? Think of it as trying to build a house on unsuitable foundations. You might be an expert with clay and comfortable using it, but for the foundation, concrete would be better? That way you do not come back and say 'my house is subsiding,'��
    Quote Originally Posted by CycloneSteve View Post
    Because I know how to create a Mail Merge letter in Word using an Access database. I don't know how to build an Access report.

    One of the saddest and most common threads I tend to see on these types of forum is that everyone is clearly an expert and expects everyone else to be an expert as well. I have some basic knowledge of Access and creating Mail Merge documents in Word so that is the way I chose to do it. All I asked for was some assistance with my problem and I get met with "elitist" comments like yours. If I knew how to build an Access report and complete my task the way you are suggesting, I wouldn't be on here asking for assistance in the first place.

    You aren't making the environment for newbies particularly welcoming.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  12. #12
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Steve

    In the attached the tables have been normalised.

    I created a Main Form for the Athletes details and then a related subform to record the Events for the Athlete.

    Study the relationship Diagram.

    I also created a Query for the Athletes Events.

    I then created a Report based on the query.

    Come back with any questions.
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Just asked question to clarify situation, to get to 'know' you and your expertise. Why get so snippy? And didn't I give you a solution in post #2?

    Building an Access report should be simpler than all the machinations involved with mail merge. And I see mike has gone and done that for you. Aren't you the lucky one!!
    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 Mathis1225 in forum Access
    Replies: 1
    Last Post: 06-11-2018, 07:08 AM
  2. Mail Merge issue
    By johnson8809 in forum Access
    Replies: 4
    Last Post: 02-02-2015, 11:13 AM
  3. Replies: 1
    Last Post: 07-17-2014, 05:51 PM
  4. Help with Mail Merge please ?
    By bellevue in forum Forms
    Replies: 21
    Last Post: 02-16-2013, 07:05 AM
  5. Mail Merge
    By Nixx1401 in forum Access
    Replies: 1
    Last Post: 02-15-2010, 10:51 AM

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