The following routine is a very basic listing of all Artists in the MM database, with simple statistics and a summary of the following:
- Album authored by them (Albums)
- Album they appeared on (Appearances)
- Songs they authored (Songs)
The following can be plugged into anything that writes to an MM Web Document (indicated by DOC).
Note: Please take the HTML formatting and order with a grain of salt. I just whipped that in real quick to separate out the items and make them easier to read - the HTML is by no means in final form.
I need help in two ways:
(1) I would like to know whether or not the routine is as simplified as it can be for its purposes of both accurately counting the designated info and displaying the designated info for each artist? In other words, are there simpler queries I could have used?
(2) I need to consolidate the APPEARANCES list and count. The other counts only show up for unique instances simply by the nature of the query. But the query I have used for APPEARANCES causes all appearances to show up. Therefore, if an artist appears on an album twice, then the artist will have the album listed twice under ALBUM APPEARANCES. I would like it to be listed only once.
Code (may be easier to understand if you paste in a text editor with word wrap turned off):
Code: Select all
Set colArtists = SDB.Database.OpenSQL ("SELECT ID, Artist FROM Artists ORDER BY Artist")
While NOT colArtists.EOF
If NOT colArtists.StringByIndex(0) = "0" Then
Set cntSongs = SDB.Database.OpenSQL ("SELECT COUNT(*) FROM Songs WHERE Songs.IDArtist = " & colArtists.StringByIndex(0))
Set cntAlbums = SDB.Database.OpenSQL ("SELECT COUNT(*) FROM Albums WHERE Albums.IDArtist = " & colArtists.StringByIndex(0))
Set cntAppearances = SDB.Database.OpenSQL ("SELECT COUNT(*) FROM Songs INNER JOIN Albums ON Songs.IDAlbum = Albums.ID WHERE Songs.IDArtist = " & colArtists.StringByIndex(0) & " AND Albums.IDArtist <> " & colArtists.StringByIndex(0))
' Artist Summary
Doc.Write "<br /><span style='color: blue; font-size: large;'>" & UCase(colArtists.StringByIndex(1)) & "</span> (Appears: <b>" & cntAppearances.StringByIndex(0) & "</b>, Albums: <b>" & cntAlbums.StringByIndex(0) & "</b>, Songs: <b>" & cntSongs.StringByIndex(0) & "</b>)<br />"
' List Albums
Set colAlbums = SDB.Database.OpenSQL ("SELECT Album FROM Albums WHERE Albums.IDArtist = " & colArtists.StringByIndex(0))
If NOT colAlbums.EOF Then Doc.Write "<br /> <b>ALBUMS</b><br />"
While NOT colAlbums.EOF
Doc.Write " " & colAlbums.StringByIndex(0) & "<br />"
colAlbums.Next
Wend
' List Album Appearances
Set colAppearances = SDB.Database.OpenSQL ("SELECT Album FROM Albums INNER JOIN Songs ON Songs.IDAlbum = Albums.ID WHERE Songs.IDArtist = " & colArtists.StringByIndex(0) & " AND Albums.IDArtist <> " & colArtists.StringByIndex(0))
If NOT colAppearances.EOF Then Doc.Write "<br /> <b>ALBUM APPEARANCES</b><br />"
While NOT colAppearances.EOF
Doc.Write " " & colAppearances.StringByIndex(0) & "<br />"
colAppearances.Next
Wend
' List Songs
Set colSongs = SDB.Database.OpenSQL ("SELECT SongTitle FROM Songs WHERE Songs.IDArtist = " & colArtists.StringByIndex(0))
If NOT colSongs.EOF Then Doc.Write "<br /> <b>SONGS</b><br />"
While NOT colSongs.EOF
Doc.Write " " & colSongs.StringByIndex(0) & "<br />"
colSongs.Next
Wend
'Set colSongs = SDB.Database.OpenSQL ("SELECT SongTitle FROM Songs WHERE Songs.IDArtist = " & colArtists.StringByIndex(0))
'While NOT colSongs.EOF
' Doc.Write " " & colSongs.StringByIndex(0) & "<br>"
' colSongs.Next
'Wend
End If
colArtists.Next
Wend
Thanks for your help!
The following routine is a very basic listing of all Artists in the MM database, with simple statistics and a summary of the following:
- Album authored by them (Albums)
- Album they appeared on (Appearances)
- Songs they authored (Songs)
The following can be plugged into anything that writes to an MM Web Document (indicated by DOC).
[i]Note: Please take the HTML formatting and order with a grain of salt. I just whipped that in real quick to separate out the items and make them easier to read - the HTML is by no means in final form.[/i]
I need help in two ways:
(1) I would like to know whether or not the routine is as simplified as it can be for its purposes of both accurately counting the designated info and displaying the designated info for each artist? In other words, are there simpler queries I could have used?
(2) I need to consolidate the APPEARANCES list and count. The other counts only show up for unique instances simply by the nature of the query. But the query I have used for APPEARANCES causes all appearances to show up. Therefore, if an artist appears on an album twice, then the artist will have the album listed twice under ALBUM APPEARANCES. I would like it to be listed only once.
Code (may be easier to understand if you paste in a text editor with word wrap turned off):
[code] Set colArtists = SDB.Database.OpenSQL ("SELECT ID, Artist FROM Artists ORDER BY Artist")
While NOT colArtists.EOF
If NOT colArtists.StringByIndex(0) = "0" Then
Set cntSongs = SDB.Database.OpenSQL ("SELECT COUNT(*) FROM Songs WHERE Songs.IDArtist = " & colArtists.StringByIndex(0))
Set cntAlbums = SDB.Database.OpenSQL ("SELECT COUNT(*) FROM Albums WHERE Albums.IDArtist = " & colArtists.StringByIndex(0))
Set cntAppearances = SDB.Database.OpenSQL ("SELECT COUNT(*) FROM Songs INNER JOIN Albums ON Songs.IDAlbum = Albums.ID WHERE Songs.IDArtist = " & colArtists.StringByIndex(0) & " AND Albums.IDArtist <> " & colArtists.StringByIndex(0))
' Artist Summary
Doc.Write "<br /><span style='color: blue; font-size: large;'>" & UCase(colArtists.StringByIndex(1)) & "</span> (Appears: <b>" & cntAppearances.StringByIndex(0) & "</b>, Albums: <b>" & cntAlbums.StringByIndex(0) & "</b>, Songs: <b>" & cntSongs.StringByIndex(0) & "</b>)<br />"
' List Albums
Set colAlbums = SDB.Database.OpenSQL ("SELECT Album FROM Albums WHERE Albums.IDArtist = " & colArtists.StringByIndex(0))
If NOT colAlbums.EOF Then Doc.Write "<br /> <b>ALBUMS</b><br />"
While NOT colAlbums.EOF
Doc.Write " " & colAlbums.StringByIndex(0) & "<br />"
colAlbums.Next
Wend
' List Album Appearances
Set colAppearances = SDB.Database.OpenSQL ("SELECT Album FROM Albums INNER JOIN Songs ON Songs.IDAlbum = Albums.ID WHERE Songs.IDArtist = " & colArtists.StringByIndex(0) & " AND Albums.IDArtist <> " & colArtists.StringByIndex(0))
If NOT colAppearances.EOF Then Doc.Write "<br /> <b>ALBUM APPEARANCES</b><br />"
While NOT colAppearances.EOF
Doc.Write " " & colAppearances.StringByIndex(0) & "<br />"
colAppearances.Next
Wend
' List Songs
Set colSongs = SDB.Database.OpenSQL ("SELECT SongTitle FROM Songs WHERE Songs.IDArtist = " & colArtists.StringByIndex(0))
If NOT colSongs.EOF Then Doc.Write "<br /> <b>SONGS</b><br />"
While NOT colSongs.EOF
Doc.Write " " & colSongs.StringByIndex(0) & "<br />"
colSongs.Next
Wend
'Set colSongs = SDB.Database.OpenSQL ("SELECT SongTitle FROM Songs WHERE Songs.IDArtist = " & colArtists.StringByIndex(0))
'While NOT colSongs.EOF
' Doc.Write " " & colSongs.StringByIndex(0) & "<br>"
' colSongs.Next
'Wend
End If
colArtists.Next
Wend[/code]
Thanks for your help!