Concatenate records and GROUP BY in Access

Question

I have a table like this:

title               part                   desc
Blah This           1                      This begins the
Blah This           2                      example table.
Some Record         1                      Hello
Another             1                      This text extends a bit
Another             2                      further so it is in
Another             3                      another record in the
Another             4                      table

In Access, I am looking to build a query/SQL to GROUP BY title and concatenate the desc field so it looks like this:

title              desc
Blah This          This begins the example table.
Some Record        Hello
Another            This text extends a bit further so it is in another record in the table

How can this be done with just SQL (no VBA/scripts)? FOR XML PATH does not seem to work in Access, only SQL Server. I've tried VBA here How to improve efficiency of this query & VBA?, but it is simply too slow.

Or is there a function that can be used that is not continually run while the query is already open?

Take a look at this. Not sure if FOR XML PATH works in Access, but if it does, this is your answer: http://stackoverflow.com/questions/14082863/concatenation-of-strings-by-for-xml-path — Kyle Hale, Mar 25 '13 at 21:20
There's no way to do this with just Sql in Access. [You'll need to use a function](http://wiki.lessthandot.com/index.php/Concatenate_a_List_into_a_Single_Field_%28Column%29). — Michael Fredrickson, Mar 25 '13 at 21:25

score 6 · Accepted Answer · edited May 23 '17 at 10:29

6

There is no Group_Concat in Access :/. Probably there is no solution that excludes VBA.
Here is one possible: Concatenating Rows through a query

edited May 23 '17 at 10:29

Community

1
1

answered Mar 25 '13 at 21:26

www

4,365
1
23
24

score 1 · Answer 2 · answered Apr 15 '20 at 18:55

Here is a rough outline of how to address this using VBA; it performs faster by running a single DB query for the detail records:

Set rsParent = CodeDb.OpenRecordset("SELECT * FROM MainTable ORDER BY HeaderID")
Set rsDetail = CodeDb.OpenRecordset("SELECT * FROM DetailTable ORDER BY HeaderID")
Do Until rsParent.EOF
  ...
  myString = rsParent!MainHeaderName & AggregateDetails(rsDetail, rsParent!HeaderID)
  rsParent.MoveNext
Loop
...

Function AggregateDetails(rsDetail as Recordset, HeaderID as String) as String
   Dim DetailString as String

   Do While rsDetail!HeaderID = HeaderID
      DetailString = DetailString & ", " & rsDetail!DetailName
      rsDetail.MoveNext
      If rsDetail.EOF Then Exit Do
   Loop
   AggregateDetails = DetailString
End Function

score 0 · Answer 3 · edited Jan 17 '23 at 17:53

Public Function AggregateDetails(TableName As String, _
                                 ConcatField1Name As String, ConcatField1Value As String, _
                                 ConcatField2Name As String, ConcatField2Value As String, _
                                 ConcatField3Name As String, ConcatField3Value As String, _
                                 ConcatResultField As String) As String
                                 
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strResult As String
    Dim i As Integer
    Dim n As Integer
    
    strSQL = "SELECT " & ConcatResultField & " FROM " & TableName & " WHERE " & ConcatField1Name & " = '" & ConcatField1Value & "'"
    If ConcatField2Name <> "" Then strSQL = strSQL & " AND " & ConcatField2Name & " = '" & ConcatField2Value & "'"
    If ConcatField3Name <> "" Then strSQL = strSQL & " AND " & ConcatField3Name & " = '" & ConcatField3Value & "'"

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSQL)
    
    
    i = 1
    
    strResult = ""
    
    If rs.RecordCount > 0 Then
        rs.MoveLast
        n = rs.RecordCount
        rs.MoveFirst
        Do Until rs.EOF
            strResult = strResult & rs.Fields(ConcatResultField).Value
            If i < n Then strResult = strResult & ", "
            rs.MoveNext
            i = i + 1
        Loop
    End If
    
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
    
    AggregateDetails = strResult
    
End Function

Please try to add some supporting information to your post that explains what your code does. There also appears to be issues with formatting in your answer as well, take a look at stackoverflow.com/editing-help — jv-k, Jan 16 '23 at 15:33

Concatenate records and GROUP BY in Access

3 Answers3

Linked