Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62

    Unanswered: Concatenate values into a Single Column

    Hello,
    I need help to Concatenate ProductGroup values based on SoldTo
    into a Single Column and separate each value by comma
    using Access code.
    I need to insert this concat data into new table

    Here is my table...
    SOLDTO ProductGroup
    22 LEGWR
    22 DANCE
    24 DANCE
    24 LEGWR
    26 LEGWR
    32 DANCE
    32 LEGWR
    51 LEGWR
    51 ACTIVE
    51 DANCE

    And the result has to be like below:
    22 LEGWR , DANCE
    24 DANCE , LEGWR
    .......
    51 LEGWR , ACTIVE , DANCE

    Thank you so much

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why would you want to do this exactly?
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62
    I need to list ProdGroups per each SoldTo using Single line format.

    Here the code I used. Thank you.

    ''''''''''''''''''
    Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot)

    If Not rst.BOF And Not rst.EOF Then

    rst.MoveFirst

    strColumn1 = rst!SoldTo
    strColumn2 = rst!ProductGroup

    rst.MoveNext

    Do Until rst.EOF
    If strColumn1 = rst!SoldTo Then
    strColumn2 = strColumn2 & ", " & rst!ProductGroup
    Else
    sSQL = "INSERT INTO tblCopy (SoldTo, ProductsCarried) " _
    & "VALUES('" & strColumn1 & "','" & strColumn2 & "')"
    db.Execute sSQL
    strColumn1 = rst!SoldTo
    strColumn2 = rst!ProductGroup
    End If
    rst.MoveNext
    Loop

    ' Insert Last Record
    sSQL = "INSERT INTO tblCopy (SoldTo, ProductsCarried) " _
    & "VALUES('" & strColumn1 & "','" & strColumn2 & "')"
    db.Execute sSQL
    End If

  5. #5
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by georgev
    Why would you want to do this exactly?
    Quote Originally Posted by ypal
    I need to list ProdGroups per each SoldTo using Single line format.
    That's not really a justification, is it.

    For the benefit of other readers; this may seem like an attractive prospect and potential solution to your problems, but before going ahead with it you may wish to post a question asking for opinions.

    This is rarely the best solution to a problem
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •