0

This is not my real-work scenario but let's say that I have a table named "tbl-albums" that has the following fields:

  • albumID
  • artist-albums
  • songlist-albums (memo field)

And I have another table named tbl-songs that has the following fields:

  • songID
  • albumID-song (related to the table above)
  • tracknumber-song
  • name-song

I need to know how to write an UPDATE QUERY that will take all the song names from [tbl-songs].[name-song] and append them to the related [tbl-albums].[songlist-albums] field, one right after the other separated by a (visible) comma , and in track order.

So let’s take U2’s album Joshua tree that has the following songs on it.

  1. Where the Streets Have No Name
  2. I Still Haven't Found What I'm Looking For
  3. With or Without You
  4. Bullet the Blue Sky
  5. Running to Stand Still
  6. Red Hill Mining Town
  7. In God's Country
  8. Trip Through Your Wires
  9. One Tree Hill
  10. Exit
  11. Mothers of the Disappeared

For the Joshua tree albums ID: I want [tbl-albums].[songlist-albums] field to look like this after I run the query:

Where the Streets Have No Name, I Still Haven't Found What I'm Looking For, With or Without You, Bullet the Blue Sky, Running to Stand Still, Red Hill Mining Town, In God's Country, Trip Through Your Wires, One Tree Hill, Exit, Mothers of the Disappeared.

Is there a way to take records in a table and append them into one field of a related record like that?

user3293447
  • 3
  • 3
  • possible duplicate of [Combine values from related rows into a single concatenated string value](http://stackoverflow.com/questions/13278590/combine-values-from-related-rows-into-a-single-concatenated-string-value) – Gord Thompson Mar 05 '14 at 15:38
  • possible duplicate of http://stackoverflow.com/questions/92698/combine-rows-concatenate-rows/93863#93863 – Fionnuala Mar 05 '14 at 17:37

0 Answers0