Concatenate Results of Query

[bryan0]

Hello, I have a simple query that returns a column of items, but i would like to concatenate them all into a single string if possible.

For example, this query:

SELECT CITY FROM CITY_STATE WHERE STATE = 'California';

Would return:
Sacramento
Oakland
Los Angeles
San Diego

However, I want a comma separated string like "Sacramento, Oakland, Los Angeles, San Diego".



I did a lot of searching and found solutions using COALESCE, but that function is not supported in MS Access. Any ideas would be appreciated!

[orange]

see this

But why this format. Suppose there are 4000 cities in California??

[LeightonStew]

Hello, I have a simple query that returns a column of items, but i would like to concatenate them all into a single string if possible.

For example, this query:

SELECT CITY FROM CITY_STATE WHERE STATE = 'California';

Would return:
Sacramento
Oakland
Los Angeles
San Diego

However, I want a comma separated string like "Sacramento, Oakland, Los Angeles, San Diego".

I did a lot of searching and found solutions using COALESCE, but that function is not supported in MS Access. Any ideas would be appreciated!

====================
As a Macro, maybe something haywire such as creating a cross tab query, exporting the results as CSV, reading them back in.

Otherwise I have used VBA to write a custom function. For example:http://allenbrowne.com/func-concat.html

[LeightonStew]

Stack Overflow has a related post... and a pretty elegant ADO approach:
http://stackoverflow.com/questions/1...ield-in-access