Concatenating field data into string via Access SQL

Question

Imagine the following table

ID | Name
----------
1  | Shaun
1  | Terrence
2  | Jessica

I need to concatenate the string data in Name based on ID

ID | Name
-----------
1  | Shaun, Terrence
2  | Jessica

I am using an access database. I was thinking I could do a pivot transform and try to concatenate those fields but the problem is its hard to dynamically loop through the total field count. Any ideas?

**Edit: Order does not matter, I just want to concatenate based on ID with , and space being the delimiter. I am calling this sql code using an ADO connection from excel vba.

Does the order matter? Like "Terrence, Shaun" would OK too? What if there's 10 ID's with the same value? Do you just want to concatenate all names with the same ID? Please provide more details. — Rene, Feb 14 '18 at 04:33
Possible duplicate of [Ms Access Query: Concatenating Rows through a query](https://stackoverflow.com/questions/5517233/ms-access-query-concatenating-rows-through-a-query) — Erik A, Feb 14 '18 at 07:50
Possible duplicate of [Combine values from related rows into a single concatenated string value](https://stackoverflow.com/questions/13278590/combine-values-from-related-rows-into-a-single-concatenated-string-value) — Andre, Feb 14 '18 at 08:02
So Erik you are right those are definitely applicable links. The problem is they use functions but unfortunately I am calling this sql code from Excel ADO connection to the access db. I will edit my question above to include that. — Irish Redneck, Feb 14 '18 at 16:06
You did not answer my previous question as for how many values you might want to concatenate based on ID. If it's more then 2, you'll need to write some VBA code. — Rene, Feb 14 '18 at 20:17
Okay you are right it must use VBA to be dynamic, but what if I put a limit to the max amount of values to ever be concatenated is 10. — Irish Redneck, Feb 14 '18 at 22:10

score 1 · Answer 1 · edited Feb 15 '18 at 12:01

This is a similar problem I had recently trying to pivot a two column table; MS Access convert and summarise two rows into columns with unique names

'Name' is a reserved word and 'ID' is usually a auto-index with unique numbers so I changed your columns to UserID and UserName respectively.

There are some problems with creating the answer in a single subquery so I ended up doing this:

Create a temporary table with an index:

SELECT t1.UserID, t1.UserName, 
    (SELECT COUNT(*) + 1 
     FROM Table1 t2 
     WHERE t1.UserID = t2.UserID and t2.UserName < t1.UserName) AS [Index]  
INTO Table1_indexed 
FROM Table1 AS t1;

create a temporary cross tab table:

TRANSFORM First(Table1_indexed.UserName) AS FirstOfUserName 
SELECT Table1_indexed.UserID FROM Table1_indexed 
GROUP BY Table1_indexed.UserID 
PIVOT Table1_indexed.Index;

concatenate the name fields

SELECT Table1_crosstab.UserID, Table1_crosstab.[1], Table1_crosstab.[2], 
       IIf([1] Is Not Null,[1]) & IIf([2] Is Not Null,", " & [2]) AS ConcatenatedName 
FROM Table1_crosstab;

If you have more than two name fields you could adjust the concatenate query to the maximum number you expect.

It might be possible to merge these steps into a single query but I've not yet found a way.

Thanks for this response. I to was working on a similar query using transform, The issues does come down to dynamically know the max number of columns. Given this most likely isnt possible to do then just making an assumption to a maximum amount is probably best. — Irish Redneck, Feb 15 '18 at 16:06

score 0 · Answer 2 · answered Nov 07 '22 at 22:41

You can create a Visual Basic Function and call it from your query, e.g. something like this (assuming your table is called Names):

Public Function ListOfNames(id as Integer) As String

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Select Name from Names where ID=" & id, DbOpenSnapshot)

ListOfNames = ""

If Not (rs.EOF and rs.BOF) Then
    rs.MoveFirst
    Do Until rs.EOF = True
        If (Len(ListOfNames) > 0) Then
            ListOfNames = ListOfNames & “, “
        End If
        ListOfNames = ListOfNames & rs!Name
        rs.MoveNext
    Loop
End If
rs.Close

End Function

Then you can for instance call the function from your query:

SELECT ID, ListOfNames([ID]) as Name From Names Group By ID

Concatenating field data into string via Access SQL

2 Answers2