Results 1 to 5 of 5
  1. #1
    Bcanfield83 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    81

    Question Grouped concatenated values..?

    Good Afternoon,

    I'm trying to figure out how to take a set of values in a table and concatenate by the name in Column A.
    Below is an example of what I'm talking about.. Should be faily easy, right? I'm willing to use VBA as well.

    BEFORE:



    Code:
    jdoe  11
    jdoe  12
    jdoe  13
    jdoe  17
    asmith 18
    asmith 21
    asmith 23
    ljones 10
    ljones 11
    ljones 14
    AFTER:

    Code:
    jdoe 11, 12, 13, 17
    asmith 18, 21, 23
    ljones 10, 11, 14

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    Bcanfield83 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    81
    Thanks! I think I'm close now.. using the function in the link provided, though for some reason it's prompting for a parameter value when I try to run the query..?

    Code:
    SELECT tblUser_Import.UserID, ConcatRelated([Loc_Number],[tblUser_Import]) AS locs
    FROM tblUser_Import

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Function argument parameters must be within quote marks. The [ ] characters are optional in this case because names do not have spaces nor special characters (underscore is acceptable exception). You will need filter criteria so each record concatenates only values associated with the UserID. Assume UserID is a number field. Concatenate UserID field so value of current record is used in the function call. Then if you want a unique record for each UserID, use GROUP BY. The resulting SQL should look like:

    SELECT tblUser_Import.UserID, ConcatRelated("Loc_Number", "tblUser_Import", "UserID=" & [UserID]) AS locs FROM tblUser_Import GROUP BY UserID, ConcatRelated("[Loc_Number]", "[tblUser_Import]", "UserID=" & [UserID]);

    Or try:

    SELECT DISTINCT tblUser_Import.UserID, ConcatRelated("Loc_Number", "tblUser_Import", "UserID=" & [UserID]) AS locs FROM tblUser_Import;
    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
    Bcanfield83 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    81
    Thanks! It's working like a charm now!

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

Similar Threads

  1. Replies: 1
    Last Post: 09-23-2018, 07:10 AM
  2. Replies: 4
    Last Post: 11-03-2012, 10:44 PM
  3. Min/Max of Transposed and Grouped Values
    By Lady_Jane in forum Queries
    Replies: 3
    Last Post: 06-20-2011, 08:55 AM
  4. Replies: 8
    Last Post: 05-25-2010, 04:50 AM
  5. Sum values in concatenated fields
    By jdrubins in forum Reports
    Replies: 4
    Last Post: 09-01-2009, 07:20 AM

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