Subquery Problem

[VSCurtis]

Hi Guys,

I have a problem with a subquery that I can't seem to figure out.

I've built a media database in Access. I'm in the process of creating some queries that will be used for data display purposes. They will be converted to views when the database is upsized to SQL Server.

I have one particular query that is giving me real problems. The query itself is very simple except for one piece which requires a subquery. The query produces basic totals for each Artist:

Total Albums: Count(Title)
Total Songs: Sum(TrackCount)
Total Playing Time: Sum(TotalTime)

I have this part of the query done and it produces the expected values. There is one addition element that I need to add to this query which is the album cover art field for the oldest album for each artist. I use Min(ReleaseYear) to get the Release Year of the oldest album for each Artist. What I'm trying to do is retrieve the Album Cover Art field for this album for each Artist. The field's name is CoverArt and it's an OLE Object field.



I know that I need a subquery to do this and I've tried it every way I can think of with no success.I would prefer to do this in the same query. My subquery skills are a bit rusty and if someone would be kind enough to assist me with this part of the query I would be most grateful.

Here's the query:

SELECT tblArtists.Artist, Min(tblArtistTitles.YearReleased) AS MinOfYearReleased, Count(tblTitles.Title) AS TotalAlbums, Sum(tblMediaLocations.TrackCount) AS TotalSongs, Sum(tblMedia.TotalTime) AS TotalPlayTime
FROM tblTitles INNER JOIN (((tblArtists INNER JOIN tblArtistTitles ON tblArtists.ArtistID = tblArtistTitles.ArtistID) INNER JOIN tblMediaLocations ON (tblArtistTitles.MediaTypeID = tblMediaLocations.MediaTypeID) AND (tblArtistTitles.YearReleased = tblMediaLocations.YearReleased) AND (tblArtistTitles.TitleID = tblMediaLocations.TitleID) AND (tblArtistTitles.ArtistID = tblMediaLocations.ArtistID)) INNER JOIN tblMedia ON (tblMediaLocations.DiskNumber = tblMedia.DiskNumber) AND (tblMediaLocations.MediaTypeID = tblMedia.MediaTypeID) AND (tblMediaLocations.YearReleased = tblMedia.YearReleased) AND (tblMediaLocations.TitleID = tblMedia.TitleID) AND (tblMediaLocations.ArtistID = tblMedia.ArtistID)) ON tblTitles.TitleID = tblArtistTitles.TitleID
GROUP BY tblArtists.Artist;

Thanks so much for your help.

[June7]

Maybe TOP N parameter would help. Review http://allenbrowne.com/subquery-01.html#TopN

[VSCurtis]

Tried that too, no luck

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[VSCurtis]

Here's a copy of my DB

[June7]

You want the oldest album cover for each artist included in the aggregate query? What is the name of the query you posted? I am not finding exact match.

I added an autonumber field to tblArtistTitles to provide a unique record ID. Then query to pull the record of oldest album year for each artist:

SELECT ArtistID, TitleID, YearReleased, CoverArt FROM tblArtistTitles WHERE ID IN
(SELECT TOP 1 ID FROM tblArtistTitles AS Dupe WHERE Dupe.ArtistID=tblArtistTitles.ArtistID ORDER BY Dupe.ArtistID, Dupe.YearReleased, Dupe.TitleID);

Join that query to aggregate query. Need to include ArtistID in the aggregate query.


I've never used OLEObject field, never could get OLEObject control to do what I want. I use Image Control to dynamically display images. I set its ControlSource property to an attachment field or to a text field that has file path of external image file.

Using special characters (@, #) as part of an ID value is really unusual. This might cause some issues.

[VSCurtis]

The query name is qryArtistTotals. There are some additional fields in the query such as Artist and Min(ReleaseYear) but this is the query that produces the required totals for each artist. Thank you for your other suggestions, but I'm content with my database design the way it is.

[VSCurtis]

The @ and # being used as part of keys is for a reason. The first character of each key is the first letter of the artist or title. In the case where a title starts with a number I use the # and in the case where a title starts with a special character I use the @. I don't like using the autonumber field as a key so this is my solution. Thanks for your advice.

[June7]

I added the autonumber field because it was the only way I could get the nested query to work. It does not have to be the key field. So none of the table relationships are changed. You will find a unique identifier like the autonumber useful for various nested queries.

[VSCurtis]

Well, I have yet to find a good use for them myself. They are a lazy excuse for defining a real key. My Sys Admin and DB Admin instructors in college taught me that a key field should be a unique piece of DATA not some magic number that has no meaning. Anyway, the ReleaseYear identifies the unique record in question for each artist. Thanks for the assist I appreciate it.

[June7]

Yes, but the year is not unique in the table. Multiple records have the same year. Still need a unique ID for the query to work. As I said, the autonumber is not being used as primary key.

There are competing points of view on use of autonumber as primary key. I've developed only in Access but I understand the autonumber may be unique to Access. I have used both autonumber and custom generated ID as primary key. I personally do not like compound keys and use an autonumber to avoid them.

Good luck with your project.

[VSCurtis]

Year is unique to the album in question for each Artist. An Artist can only have one album that's the oldest so it should suit my needs. I don't like compound keys to a point either, but I like Autonumber even less. This DB is going to be upsized to SQL Server once I'm done creating the queries I need so that I'll have greater flexibility in creating my DAL as well as the ability to create Stored Procs. I don't mean to seem stubborn in my point of view I guess I'm something of a purist. My instructors at DeVry were very good at what they did and what they taught me has served me well. Thanks so much for your help.

[June7]

Year is unique to each title for each artist but it is not unique in the table. The nested query needs a unique record identifier. I gave it one more try without the autonumber field. Required using 3 fields to produce unique ID. See if this gets desired output.

SELECT ArtistID, YearReleased, TitleID, CoverArt FROM tblArtistTitles
WHERE (((ArtistID & YearReleased & TitleID) In (SELECT TOP 1 ArtistID & YearReleased & TitleID FROM tblArtistTitles AS Dupe WHERE Dupe.ArtistID=tblArtistTitles.ArtistID ORDER BY Dupe.ArtistID, Dupe.YearReleased, Dupe.TitleID)));

Wasn't trying to change your point of view nor did I even suggest the autonumber should be primary key. But perhaps you can see that autonumber made the query simpler.

I think SQL table can be set to generate a unique record ID but doesn't have the same constraints as Access autonumber. http://msdn.microsoft.com/en-us/libr...v=vs.110).aspx

And really, what is 'purist' about not using autonumber? I suggest the DeVry instructors were too rigid and inflexible on this point. I have found both autonumber and custom ID useful in database structure.

[VSCurtis]

I did not say that ReleaseYear by itself was a unique value in the table, nor did I say that I was looking for the oldest album in the table. I said that I was looking for the oldest album for each artist so that I can retrieve the album cover art field for that album. The primary key for the ArtistTitles table is ArtistID, TitleID, ReleaseYear, and MediaTypeID so your concatenation of these fields is of no great surprise to me. ReleaseYear is included in the key because it is possible for an Artist to have more than one album by the same title and MediaTypeID is part of the key to distinguish different media by the same Artist with the same title such as an album and a related live concert video.

I appreciate your efforts to help me resolve this issue.

[June7]

Both of the queries I suggested (with and without autonumber) accomplish retrieving "the oldest album for each artist". If you want the oldest album for each title for each artist, that is different.

+