CODEWITHSUNDEEP
sqlms-accessconcatenation
JBurace
JBurace

Reputation: 5643

Concatenate records and GROUP BY in Access

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?

Upvotes: 10

Views: 40258

Answers (3)

Hans
Hans

Reputation: 1

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

Upvotes: 0

Kit.net
Kit.net

Reputation: 31

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

Upvotes: 1

www
www

Reputation: 4391

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

Upvotes: 6

Related Questions