Search code examples
sqlms-accessms-access-2003

Create a comma-separated string from multiple rows of one 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?

Solution

  • 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 http://allenbrowne.com/func-concat.html However, once you use a UDF, the query is no longer viable outside Access.