GROUP_CONCAT analogue or any simulation 1

[jfort]

Is there any analogue or simulation to the wonderful MySQL’s GROUP_CONCAT function? Say, I have two columns on the table: id, hobby. Every id may have 1 or more hobbies, like:

Id Hobby
1 Hiking
1 Dancing
2 Dancing
2 Swimming
3 Horse back riding

So, in MySQL I’d use
SELECT id, GROUP_CONCAT(hobby ORDER BY hobby SEPARATOR ’, ’) AS hobbies
FROM my_table
GROUP BY id;

The result would be:
Id Hobbies
1 Dancing, Hiking,
2 Dancing, Swimming,
3 Horse back riding,

Is there any way to do it in MS Access?
Thanks in advance!

 

[Golom]

There's no strictly SQL way to do that in Access. There is however faq701-3499 that provides a code-based function that will accomplish that result.

 

[dhookom]

There is a generic function to do this at faq701-4233. For a sample mdb with this function, check

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]