Create a comma separated string from multiple rows of a column?


I've an access table like this

ID | UserName | CarBrand
0    Peter      VW
1    Peter      Ferrari
2    Mike       Audi
3    Peter      Dodge
4    Heidi      BMW
5    Heidi      Ford

I need the names from the CarBrand field as a comma separated list for a report.

Is there a way (without VB, maybe using a COALESCE alternative?) to create a comma-separated string like this, without the Name: part?

Peter: VW, Ferrari, Dodge
Mike:  Audi
Heidi: BMW, Ford

As it's for a report are there any other ways to do this, maybe using expressions in the report?

You cannot do this is Access without VBA. Coalesce does not exist, but you can write a UDF that has some of the functionality, for example However, once you use a UDF, the query is no longer viable outside Access.