is there a group_concat function in ms-access?

Question

is there a group_concat function in ms-access or something similar?

Solution 1

You should ask yourself if you need a generic solution (another is by Allen Browne) or if you need it just for the present purpose. If you really only need it this once, do it the easy way.

On a side note, when concatenating lists in VBA code, take advantage of a trick taught to me by long-time Access guru Trevor Best, and that's to stick the delimiter at the beginning of every value and then use Mid() to strip it off. Instead of this inside your loop through the child records:

  If Len(strOutput) = 0 Then
     strOutput = NewValue
  Else
     strOutput = strOutput & ", " & NewValue
  End If

...use this inside the loop:

  strOutput = strOutput & ", " & NewValue

...and then when you exit the loop, strip off the leading delimiter:

  strOutput = Mid(strOutput, 3)

This has implications all over the place and simplifies code for concatenation in a whole host of contexts.

Solution 2

There's an access function to group multiple values into one value (a custom aggregate, I guess.) The link is http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Generic%20Function%20To%20Concatenate%20Child%20Records'

but the site is down for now. If you google the href, you'll find lots of referneces and examples.

Solution 3

I found this post by Duane Hookum (a Microsoft MVP) that claims to be able to do what you want. I have not tested it though.

---

By the way, in case you are interested, this is how I found it:

First search: group_concat access lead me to this post with this answer but the link was broken.

Then I searched again after the content that the answer was attempting to link to, and found it: site:http://www.rogersaccesslibrary.com/ concatenate.

Solution 4

No. Access does not have a GROUP_CONCAT function. However, it is possible to create a VBA function which will let you pass a string containing a SQL statement and get the equivalent functionality (not that I'd recommend it but it is possible).

Taking my own personal wayback machine, here is some code I wrote back when dinosaurs ruled the Earth:

Public Function ListQuery(SQL As String _
                            , Optional ColumnDelimiter As String = " " _
                            , Optional RowDelimter As String = vbCrLf) As String
'PURPOSE: to return a combined string from the passed query
'ARGS:
'   1. SQL is a valid Select statement
'   2. ColumnDelimiter is the character(s) that separate each column
'   3. RowDelimiter is the character(s) that separate each row
'RETURN VAL:
'DESIGN NOTES:

Const PROCNAME = "ListQuery"
Const MAXROWS = 100
Const MAXCOLS = 10
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim oField As ADODB.Field
Dim sRow As cString
Dim sResult As cString

On Error GoTo ProcErr

Set sResult = New cString
Set sRow = New cString
Set oConn = GetADOConn()

sResult.Clear
Do Until oRS.EOF
    sRow.Clear

    For Each oField In oRS.Fields
        With sRow
            If .Length > 0 Then
                .Append ColumnDelimiter
            End If

            .Append Nz(oField.Value)
        End With
    Next oField

    sRow.Trim
    If sRow.Length > 0 Then
        With sResult
            .Append sRow
            .Append RowDelimter
        End With
    End If

    oRS.MoveNext
Loop
oRS.Close
oConn.Close

With sResult
    If .Right(Len(RowDelimter)).Value = RowDelimter Then
        .Length = .Length - Len(RowDelimter)
    End If
End With

FunctionResult:
    ListQuery = sResult.Value

CleanUp:
    Set sResult = Nothing
    Set sRow = Nothing
    Set oField = Nothing
    Set oRS = Nothing
    Set oConn = Nothing

Exit Function
ProcErr:
    ' logging  code...
    Resume CleanUp

End Function

The GetADOConn function is a centralized function to retrieve the current database connection. cString is a class that mimics the behavior of .NET's StringBuilder class but was written long before .NET was anything other than a TLD and marketing hype. Since this is getting called on every row, VBA's built-in string concatenation will be slow and thus something like a StringBuilder class is needed. The original code (which I've partially modified) had a cap on the number of rows and columns that could be used which is what the constants are all about.

View more solutions

Comments

[David-W-Fenton]

When writing code for Access, it seems to me to make much more sense to write it in DAO, as ADO is pretty much dead except for interaction with SQL Server.

[Thomas]

@David-W-Fenton - Well, one could argue that DAO is pretty dead too but I understand your point. The code is easy to alter for DAO. IIRC, the date on that code was sometime in '97, so it might have been that I was dabbling with ADO in Access since I was still doing ASP Classic stuff at the time.

[David-W-Fenton]

There is no way to argue that DAO is dead, as it's an actively developed component of Access, getting changes and enhancements to go along with alterations to the ACE in A2007 and A2010. It's ADO classic that's completely dead, superseded by ADO.NET, which cannot be used in Access (at least, not at this point).

[Alex Gordon]

@Thomas can you please reply to dave's last comment

[Thomas]

@Артём Царионов - Sure. We disagree. DAO within VBA is geared towards directly connected setups (i.e., linked tables). ADO was geared towards a disconnected approach and thus was and still is IMO a superior approach. Both, at this stage, are going to be difficult to port to a .NET environment. However, the systemic idea of designing your frontend with db calls of connect-get data-disconnect is better and will port easier than one of "always connected". In this particular problem, either DAO or ADO will work but arguing that ADO is dead but DAO is not is dubious.

[Thomas]

@Артём Царионов -Btw, the reason I mention .NET is that VBA's days themselves are numbered. At some point, MS is going to add .NET language functionality to Access and when that happens both DAO and ADO will dead.

[Thomas]

@IvanCachicatari - As I said in the post, cString is a custom class that mimics StringBuilder.