Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    outside the rim

    Unanswered: Group By Concatenate

    Is there a way (short of some fancy coding) to concatenate the values of a particular field together into one field for all the records in each group of a group by?

    For example, if my data was:
    1 "101"
    1 "102"
    1 "103"
    2 "104"
    2 "105"

    I want to group by the first value and concatenate the second value:
    1 "101,102,103"
    2 "104,105"

    Fairly easy using code, so I could write a function and use that function in the query, but that would be a horrendously inefficient query.
    have fun!

    Todd S.
    click to email

    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET

    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  2. #2
    Join Date
    Aug 2006
    I've been using a routine by Duane Hookum from 2003 for many DBs and it is not inefficient in my experience. I have one db where I have a query that has 3 different computed fields using concat. It is fairly flexible and with some pre-coding on your part to pick up the first criteria, you should be able to get it to do your job. [Be careful, in the attached some lines may be to long and wrap without proper continuation or quote characters.]

    Function Concatenate(pstrSQL As String, _
            Optional pstrDelim As String = ", ") _
            As String
    'Created by Duane Hookom, 2003
    'this code may be included in any application/mdb providing
    '   this statement is left intact
    'tblFamily with FamID as numeric primary key
    'tblFamMem with FamID, FirstName, DOB,...
    'return a comma separated list of FirstNames
    'for a FamID
    ' John, Mary, Susan
    'in a Query
    'SELECT FamID,
    'Concatenate("SELECT FirstName FROM tblFamMem
    ' WHERE FamID =" & [FamID]) as FirstNames
    'FROM tblFamily
    '======For DAO uncomment next 4 lines=======
    '====== comment out ADO below =======
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Set db = CurrentDb
        Set rs = db.OpenRecordset(pstrSQL)
    '======For ADO uncomment next two lines=====
    '====== comment out DAO above ======
        'Dim rs As New ADODB.Recordset
        'rs.Open pstrSQL, CurrentProject.Connection, _
            adOpenKeyset, adLockOptimistic
        Dim strConcat As String 'build return string
        With rs
            If Not .EOF Then
                Do While Not .EOF
                    strConcat = strConcat & _
                    .Fields(0) & pstrDelim
            End If
        End With
        Set rs = Nothing
    '====== uncomment next line for DAO ========
        'Set db = Nothing
        If Len(strConcat) > 0 Then
            strConcat = Left(strConcat, _
            Len(strConcat) - Len(pstrDelim))
        End If
        Concatenate = strConcat
    End Function
    'Samples of field entry in query
    'FirstNames: Concatenate ("SELECT FirstName & ' (' & Role & ')' FROM tblFamMem  WHERE FamID =" & [FamID])
    'FirstNames: Concatenate ("SELECT FirstName & Chr(9) & Role FROM tblFamMem  WHERE FamID =" & [FamID],Chr(13) & Chr(10))
    'FirstNames: Concatenate ("SELECT FirstName FROM tblFamMem WHERE FamID =" & [FamID])
    'FirstNames: Concatenate ("SELECT FirstName FROM tblFamMem WHERE FamID =" & [FamID] & " ORDER BY FirstName")
    'FirstNames: Concatenate ("SELECT FirstName FROM tblFamMem WHERE FamID =" & [FamID] & " AND Role in ('Mom','Dad')")

Posting Permissions

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