Crosstab query: Concatenate results rather than count

[Big Time]

I've got a crosstab query that counts the number of values in one of my
tables. However, rather than giving me the number of instances of each
record, what I would like is for each value to display, separated by a
comma. For example, in the query is a row is named BC and a column named
Vancouver, it currently displays "4" for the number of instances that
Vancouver appears combined with the BC field. However, what I would like to
display instead of the count is "Vancouver, Vancouver, Vancouver, Vancouver"
etc. I've tried changing the Total function to "First" or "Last" which sort
of works however this only gives me one record when I need all the records
to display.

If anyone can help me out with the SQL or even let me know if this can be
done, I would appreciate it. Here's a more of a visual display of the issue
(hopefully the formatting works):

What currently displays:

Column: Vancouver
Row: BC 4
Desired Display:
Column: Vancouver
Row: BC Vancouver, Vancouver, Vancouver, Vancouver
Thanks for any advice.

[David Schofield]

On Thu, 23 Sep 2004 14:53:30 -0700, "Big Time"
<bi************@remove-for-spam-hotmail.com> wrote:

I've got a crosstab query that counts the number of values in one of my
tables. However, rather than giving me the number of instances of each
record, what I would like is for each value to display, separated by a
comma. For example, in the query is a row is named BC and a column named
Vancouver, it currently displays "4" for the number of instances that
Vancouver appears combined with the BC field. However, what I would like to
display instead of the count is "Vancouver, Vancouver, Vancouver, Vancouver"
etc. I've tried changing the Total function to "First" or "Last" which sort
of works however this only gives me one record when I need all the records
to display.

If anyone can help me out with the SQL or even let me know if this can be
done, I would appreciate it. Here's a more of a visual display of the issue
(hopefully the formatting works):

What currently displays:

Column: Vancouver
Row: BC 4
Desired Display:
Column: Vancouver
Row: BC Vancouver, Vancouver, Vancouver, Vancouver
Thanks for any advice.

Don't think you can do this in SQL though I am now more cautious is
saying this after seeing Joe Cellco's stuff on nested sets.

Have a look at the Access Web under "Modules: Concatenate fields in
same table" which is at http://www.mvps.org/access/modules/mdl0008.htm

David