Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

is there a group_concat function in ms-access?

Tags:

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

like image 414
Alex Gordon Avatar asked May 17 '10 21:05

Alex Gordon


People also ask

What is Group_concat?

GROUP_CONCAT is a function which concatenates/merges the data from multiple rows into one field. It is a GROUP BY function which returns a string if the group contains at least 1 non-null value, if it does not, it returns a Null value.

Does SQL Server have Group_concat?

MySQL | Group_CONCAT() Function. The GROUP_CONCAT() function in MySQL is used to concatenate data from multiple rows into one field. This is an aggregate (GROUP BY) function which returns a String value, if the group contains at least one non-NULL value.


2 Answers

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.

like image 143
David-W-Fenton Avatar answered Oct 08 '22 01:10

David-W-Fenton


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.

like image 20
mdma Avatar answered Oct 07 '22 23:10

mdma