Help With Sub Query

[roach04]

Hi Folks,

Using Access 2003.

ArtistsTable
ID
Artist

AlbumsTable
AlbumName
LookUpToArtistsTable

My problem -- how can I write a query that will give me all the album
names for a given artist in one record?

I know if I do "SELECT ArtistsTable.Artist, AlbumsTable.AlbumName From
ArtistsTable LEFT JOIN AlbumsTable ON ArtistsTable.ID =
AlbumsTable.LookUpToArtistsTable" I can get a recordset that looks
like this:

"Beatles", "Revolver"
"Beatles", "Rubber Soul"
"Beatles", "Let It Be"

But what I'm looking for is:

"Beatles", "Revolver, Rubber Soul, Let It Be"

Any help appreciated!

Thanks,

Murray

[Jeff Smith]

try
http://www.mvps.org/access/modules/mdl0004.htm
"roach04" <mu**********@gmail.com> wrote in message
news:6a**************************@posting.google.c om...

Hi Folks,

Using Access 2003.

ArtistsTable
ID
Artist

AlbumsTable
AlbumName
LookUpToArtistsTable

My problem -- how can I write a query that will give me all the album
names for a given artist in one record?

I know if I do "SELECT ArtistsTable.Artist, AlbumsTable.AlbumName From
ArtistsTable LEFT JOIN AlbumsTable ON ArtistsTable.ID =
AlbumsTable.LookUpToArtistsTable" I can get a recordset that looks
like this:

"Beatles", "Revolver"
"Beatles", "Rubber Soul"
"Beatles", "Let It Be"

But what I'm looking for is:

"Beatles", "Revolver, Rubber Soul, Let It Be"

Any help appreciated!

Thanks,

Murray

[Justin Hoffman]

"roach04" <mu**********@gmail.com> wrote in message
news:6a**************************@posting.google.c om...

Hi Folks,

Using Access 2003.

ArtistsTable
ID
Artist

AlbumsTable
AlbumName
LookUpToArtistsTable

My problem -- how can I write a query that will give me all the album
names for a given artist in one record?

I know if I do "SELECT ArtistsTable.Artist, AlbumsTable.AlbumName From
ArtistsTable LEFT JOIN AlbumsTable ON ArtistsTable.ID =
AlbumsTable.LookUpToArtistsTable" I can get a recordset that looks
like this:

"Beatles", "Revolver"
"Beatles", "Rubber Soul"
"Beatles", "Let It Be"

But what I'm looking for is:

"Beatles", "Revolver, Rubber Soul, Let It Be"

Any help appreciated!

Thanks,

Murray

If you really need a query, you could use a crosstab query (if you don't
mind the column names):

TRANSFORM First(AlbumName) AS Album
SELECT Artist FROM
(SELECT * FROM AlbumsTable INNER JOIN ArtistsTable
ON AlbumsTable.LookUpToArtistsTable=ArtistsTable.ID) AS X
WHERE Artist="Beatles"
GROUP BY Artist PIVOT AlbumName

There may be another non-query solution, depending on your ultimate goal.