How to combine a number of Rows in Microsoft Access separated by slash

Question

I've got a table in Microsoft Access with two columns. I wish to Group the list by one of them and the Information of the other column should be written separated by a slash. My code works, but it takes very long (more than two hours). Does anybody has an idea, how to handle this Problem in another way?

I declared a function (see below the big code). In a Query I open this function with SQL

Select SQLListe("SELECT [column2] FROM [table_name] WHERE [column1] = '" & [column1] & "'";"/";"/") AS Result FROM [table_name]

As I said, it takes really long, amongst others a lot of rows in the table. But maybe someone has an idea.

Thanks!

Option Compare Database
Public Function SQLListe(ByVal SQL As String, _
                         Optional ByVal SepR As String = ";", _
                         Optional ByVal SepF As String = ";", _
                         Optional ByVal NoNullFields As Boolean = True) _
                         As String


 Dim rs As DAO.Recordset
 Dim i As Long
 Dim Res As String
 Dim Tmp As String

 Set rs = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)

 On Error Resume Next

 If Err.Number <> 0 Then
   Res = "#Fehler"
   Err.Clear
 Else
   On Error GoTo 0
   Res = ""

   Do While Not rs.EOF
     Tmp = ""

     For i = 0 To rs.Fields.Count - 1
       If Not (NoNullFields And IsNull(rs(i))) Then
         Tmp = Tmp & SepF & rs(i)
       End If
     Next

     If Tmp <> "" Then
       Res = Res & SepR & Mid(Tmp, Len(SepF) + 1)
     End If
     rs.MoveNext
   Loop

   If Res <> "" Then
     Res = Mid(Res, Len(SepR) + 1)
   End If
 End If

 If Not rs Is Nothing Then rs.Close: Set rs = Nothing

 SQLListe = Res

End Function

You might find some inspiration here : https://stackoverflow.com/questions/2852892/is-there-a-group-concat-function-in-ms-access — Thomas G, Apr 17 '19 at 13:04
Hallo and Welcom to StackOverflow. How big is your table? For normal size tables you can basically inline the concat in a join query, somthing like `T1.field1 & "/" & T2.Field2` — Siyon DP, Apr 17 '19 at 13:44
@ThomasG Thank you! I found some codes [link](http://www.rogersaccesslibrary.com/forum/generic-function-to-concatenate-child-records_topic16&SID=453fabc6-b3z9-34z6zb14-a78f832z-19z89a2c.html_italic_ that are similar to mine. I tried it, it still takes very Long..but maybe still faster, we will see. — Microsofter3000, Apr 17 '19 at 13:57
Interesting fact: If I choose the datasheet view of the query, it goes super fast. But I have to add the recors to another table, and in this case it takes really Long. — Microsofter3000, Apr 17 '19 at 14:05

How to combine a number of Rows in Microsoft Access separated by slash

0 Answers0