Create string from multiple records but one column

[Saphirah]

Hello everyone,

i have 2 tables, tbl_Task and tbl_Worker. They are connected using a 1-n relationship over the "TaskID" and "TaskID_F", so one Task can have multiple workers.
What i want to do now is, i want to show the names of all the workers, seperated by a comma for each task.

So i need to append the "WorkerNames" together, to create a string. An example would be "Paul, Anna, Sophie".

Is this possible in Access using pure SQL? I already created a VBA function to handle this, but due to the fact that i am using a continous form, i want to remove as much vba as possible.

Cheers and thanks for your help

 

[arnelgp]

create a Query and in the Query call the function that Concatenate the WorkerNames.

 

[theDBguy]

That's a tall order. Not sure SQL alone can do it. You may be stuck with using VBA in this case.

 

[jdraw]

Please show readers your VBA function to handle this.

 

[Isaac]

Is this possible in Access using pure SQL?

No, not unless you have a fixed, known number and that number is just a couple of records.
Edit: You wanted this in a SQL query with presumably an unknown # of records ... of course if you just want to concatenate control values from a form, that's different & can be easily done..

 

[arnelgp]

use Query on your Continuous Form. see this sample.

 

[Pat Hartman]

but due to the fact that i am using a continous form, i want to remove as much vba as possible.

That does not compute. What, exactly do you perceive as a problem with using VBA in continuous forms? EVERY form type has ONE and ONLY ONE current record and any code you run in the form's class module refers to that single record unless you create a recordset and loop through it but:

Me.somecontrol --- ALWAYS only refers to the current record.

 

[Saphirah]

Please show readers your VBA function to handle this.

Thats a simple recordset looping through the fields and adding them to a String. See here

Public Function getWorkersByTask(TaskID As String) As String
   With CurrentDb.OpenRecordset("SELECT * FROM tbl_Workers WHERE TaskID_F = " & TaskID)
    If Not (.EOF And .BOF) Then
        Do Until .EOF = True
            getWorkersByTask = getWorkersByTask & IIf(Len(getWorkersByTask) > 0, ", ", "") & !Name
            .MoveNext
        Loop
    End If
  End With
End Function

But this will open a recordset every time the function is called. And because i am using a continous form with relative small lines i have 2 choices.
I either put that VBA function in a query and Access calculates the values for every task beforehand, but this can be really slow depending on the amount of tasks.

The second option is to put that into a Text Box and do "=getWorkersByTask(TaskID)" but with that i get a flicker every time i scroll, because it needs to calculate the values in real time and needs half a second to show them.

SO the optimal way would be to calculate it in a query but with pure SQL. That is why i am asking if there is a way to do this, because i dont know. There are some modern SQL functions i found, but i did not manage to make them work in access so i am assuming access is not supporting them.

See here:

SQL Server: Concatenate Multiple Rows Into Single String | My Tec Bits

Combine multiple rows into one string in SQL Server. Simple query in SQL Server to concatenate multiple rows Into single string.

www.mytecbits.com

 

[Saphirah]

use Query on your Continuous Form. see this sample.

I will take a look at this now, Thank you very much

EDIT: Yeah that is my current approach. What i am asking is if there is a pure SQL way to handle this. If not that is not a problem, then i will stick to vba.
I just want to optimize my queries as much as possible, and using vba is slowing them down quite significantly

 

[Isaac]

T-SQL has some built in ways (and frankly, those ways are accompanied by their own slower speeds too), but have not heard of one in Access SQL that will work for you. You may be left with just deciding which thing the users have more tolerance for..a process that lags as it scrolls, or one that requires some initiation time at the very beginning. I'd probably go with the latter, all else being equal and just knowing what you've described.

Visual cues are everything IMO in these situations--It totally increases users' acceptance/tolerance of a "wait" scenario. I'll usually prefer a wait period during something's "startup" moment with speed afterward, vs. a quick startup and ongoing laggy-ness. Give your users a "Generating Tasks, Please Wait..." caption or something to indicate they can relax for a moment and check FB on their phone again for a second.

 

[MajP]

Recently I had a pretty complicated concatenation, so I made the choice to calculate and store this value instead of a dynamic query or calculated control. I just ensured I called the function to update the "concatenated" field when the record was updated or data changed to ensure it never got out of synch.
If you do not want to add a field to your data table then add a new table with a foreign key and the concatenated value. Either way this will be fast.

 

[Isaac]

Recently I had a pretty complicated concatenation, so I made the choice to calculate and store this value instead of a dynamic query or calculated control. I just ensured I called the function to update the "concatenated" field when the record was updated or data changed to ensure it never got out of synch.
If you do not want to add a field to your data table then add a new table with a foreign key and the concatenated value. Either way this will be fast.

That's a great example of when materializing calculations/aggregations makes sense.
Also comes to mind is having a package that runs overnight (etc) to output a file with the needed calculations...ssis etc..but that may exceed the scope of the OP resources, not sure, just an idea.