Results 1 to 8 of 8
  1. #1
    alpinegroove is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jan 2011
    Posts
    114

    Mergeing Multiple Rows per Person into One Row for Mail Merge

    I have been provided with three Excel tables from a volunteer database that can only export reports for one day at a time.


    If a volunteer is working multiple shifts that day, he/she will have multiple rows in the spreadsheet. Each row has all of the same information, except for the shift time.

    I have 3 files, each one for a different day.
    Some of the volunteers are listed in all of the files, and some of them are working more than one shift each day.

    I can combine all of the files and sort by name and get one long table.

    I need to create a mail merge, an email that I send to each volunteer with all of their shifts.
    I need each person to have one row in the table, with Shift 1 Date, Shift 1 Time, Shift 2 Date, Shift 2 Time, etc. all listed in that same row.

    Not all of the volunteers work the same number of shifts each day.

    Can this be accomplished in Access?

    My table:

    Joe Smith, 11-1-12, 9-10am
    Joe Smith, 11-1-12, 11-12pm
    Joe Smith, 11-2-12, 7-8am
    Sara Roy, 11-1-12, 9-10am
    Sara Roy, 11-1-12, 11-12pm

    Needed result:
    Joe Smith, 11-1-12, 9-10am, 11-1-12, 11-12pm, 11-2-12, 7-8am
    Sara Roy, 11-1-12, 9-10am, 11-1-12, 11-12pm

    I know this results in a un-normalized structure, but I need a one-time solution to address this situation.
    Any suggestions?
    Thank you.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    How about a function like this:

    Return a concatenated list of sub-record values
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    alpinegroove is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jan 2011
    Posts
    114
    Thank you for the reference, Paul.
    This is a bit advanced for me. Any more direction or a simpler method?
    Thanks again.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Not really. All of the solutions I've seen involve a function. Perhaps you'll find Allen's easier:

    http://allenbrowne.com/func-concat.html
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    alpinegroove is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jan 2011
    Posts
    114
    My tables (originally from Excel) don't have a primary key.
    The only thing I could match on is FullName.
    Could that work with this method?

    In Allen's method, do I need to change anything in the VBA code or would I just change the fields in the query?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In either one you just change how you call the function; the function should be copy/pasted into a standard module.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    alpinegroove is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jan 2011
    Posts
    114
    I got this to work. Strangely enough, it involved concatenating in Access and then once everything was on the same row separating it again in Excel into different columns.
    Thank you very much for pointing me in the right direction.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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. Database/Mail Merge
    By sashac in forum Access
    Replies: 0
    Last Post: 03-14-2012, 03:08 PM
  3. Mail Merge
    By DCV0204 in forum Forms
    Replies: 6
    Last Post: 12-13-2011, 09:32 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

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