Help With Sub Query

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

re: Help With Sub Query

try
http://www.mvps.org/access/modules/mdl0004.htm


"roach04" <murray.roach@gmail.com> wrote in message
news:6ae7960c.0504151944.375201ae@posting.google.c om...[color=blue]
> 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[/color]

re: Help With Sub Query

"roach04" <murray.roach@gmail.com> wrote in message
news:6ae7960c.0504151944.375201ae@posting.google.c om...[color=blue]
> 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[/color]


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.