Results 1 to 6 of 6
  1. #1
    Knarly555 is offline Novice
    Windows XP Access 2010 (version 14.0)
    Join Date
    Nov 2010
    Posts
    3

    Concatenate Multiple Rows into One

    I can't figure how to concatenate in Access. My data looks like:
    member_id, dialer job
    1, Job1
    1, Job2
    2, Job3
    2, Job4


    3, Job1
    3, Job2

    I want it to be
    member_id, dialer job
    1, (Job1,Job2)
    2, (Job3,Job4)
    3, (Job1,Job2)

    Seems like the below query should work from what I've searched on the googleverse, but I keep getting "Undefined function 'Concatenate' in expression".

    Code:
    SELECT [member_id], Concatenate("SELECT [Dialer Job] FROM [GVR Loaded Into Dialer1] WHERE member_id=""" & [member_id] & """") AS Dialer_Job 
    FROM [GVR Loaded Into Dialer1];
    Thanks for your help

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Maybe this link will help: http://allenbrowne.com/func-concat.html

  3. #3
    Knarly555 is offline Novice
    Windows XP Access 2010 (version 14.0)
    Join Date
    Nov 2010
    Posts
    3
    Thanks - but that's concatenating multiple columns together, I have multiple rows in the same column.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922

  5. #5
    Knarly555 is offline Novice
    Windows XP Access 2010 (version 14.0)
    Join Date
    Nov 2010
    Posts
    3
    I'd really rather not use VBA... Should be able to do it in a query don't you think? Seems like I've seen other people using this same type of code in their queries and worked for them... Just for some reason not working for me.

    I feel like I might need to add a concatenate module of some kind, but I don't really understand how that all works...

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    The SQL you posted needs a function called Concatenate() defined in a Standard Module somewhere. Have fun.

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

Similar Threads

  1. Editing Multiple Rows of Data with (Ctrl F)
    By Orangeworker in forum Access
    Replies: 2
    Last Post: 11-11-2010, 03:31 PM
  2. Replies: 11
    Last Post: 09-02-2010, 01:59 PM
  3. Inserting multiple rows using a value in a field
    By z1efuller1 in forum Queries
    Replies: 1
    Last Post: 01-06-2010, 11:20 AM
  4. Replies: 5
    Last Post: 12-10-2009, 10:33 PM
  5. Combine multiple rows into one cell
    By zarfx4 in forum Queries
    Replies: 8
    Last Post: 06-08-2009, 10:42 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