Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002
    Posts
    12

    Question Unanswered: Field Concatenation

    This sounds simple in theory but I have been unable to do the following transformation in a query. There is a very simple table with a key(non-unique) and one data field. My idea is to concatenate the data fields with similar keys into one field as seen below.

    Original table:
    1 Bob
    1 Bill
    1 Joe
    2 Ryan
    2 Jill
    3 Bob
    3 Jill
    4 Bob

    Desired Result
    1 Bob Bill Joe
    2 Ryan Jill
    3 Bob Jill
    4 Bob

    Has anyone ever tried to concatinate like this before? If so do you have any hints.

  2. #2
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Re: Field Concatenation

    Originally posted by ryanesnow
    This sounds simple in theory but I have been unable to do the following transformation in a query. There is a very simple table with a key(non-unique) and one data field. My idea is to concatenate the data fields with similar keys into one field as seen below.

    Original table:
    1 Bob
    1 Bill
    1 Joe
    2 Ryan
    2 Jill
    3 Bob
    3 Jill
    4 Bob

    Desired Result
    1 Bob Bill Joe
    2 Ryan Jill
    3 Bob Jill
    4 Bob

    Has anyone ever tried to concatinate like this before? If so do you have any hints.

    Let's try this one :

    Code:
    Private Sub Concatenate()
        Dim rsNumbers As New ADODB.Recordset
        Dim rsNamesPerNumber As New ADODB.Recordset
        Dim strSQL As String
        Dim concatenatedString As String
        
        strSQL = "SELECT DISTINCT Number FROM tblNames"
        
        rsNumbers.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
        
        Do While Not rsNumbers.EOF
            strSQL = "SELECT Name FROM tblNames WHERE Number = " & rsNumbers.Fields(0)
                     
            rsNamesPerNumber.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
            
            concatenatedString = ""
            
            Do While Not rsNamesPerNumber.EOF
                concatenatedString = concatenatedString & " " & rsNamesPerNumber.Fields(0)
                rsNamesPerNumber.MoveNext
            Loop
            
            rsNamesPerNumber.Close
    
            Debug.Print concatenatedString
            
            rsNumbers.MoveNext
        Loop
        
    End Sub
    If you don't understand what I'm doing, just let me know.

Posting Permissions

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