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,329
1
22
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

Concatenate records and GROUP BY in Access

2 Answers2

Linked

Related