By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 1,746 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

Help With Sub Query

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a

"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.
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.