Queries Help Please

[germ-X]

Hi, I am new to MS Access, and I cannot figure out how to perform the
following queries:
What songs by Norah Jones are currently in Car?
Which location has the least number of files? How many?
Which artist is the second-most popular in her collection based on quantity
of songs?
What are the top 5 songs in popularity based on the number of times played?

I have 5 tables and here are the relationships:
Artist
*****
Artist
ID
Location_ID

Album
*****
Album
ID
Artist_ID
Genre_ID

tblSong
*****
ID
Song
Duration
MyRating
PlayCount
Tempo
Mood
Album_ID

Location
*****
Location
ID

Genre
*****
Genre
ID

I tried many different ways for question 1, but everytime I open up tables
Song, Location, and Artist in design mode, the data wont even show. For
question 2, I tried to use the minimum function thing, but it does not work.
Did I create my relationship tables wrong, and if so, how should I modify it?
How should I approach to creating the queries?

Can someone please help me, the Help thing in MS Access is so confusing to
use.
Thanks in advance.

 

[Wolfgang Kais]

Hello "germ-X".

Hi, I am new to MS Access, and I cannot figure out how to perform
the following queries:
What songs by Norah Jones are currently in Car?
Which location has the least number of files? How many?
Which artist is the second-most popular in her collection based
on quantity of songs?
What are the top 5 songs in popularity based on the number of times
played?

I have 5 tables and here are the relationships:
Artist
*****
Artist
ID
Location_ID

Album
*****
Album
ID
Artist_ID
Genre_ID

tblSong
*****
ID
Song
Duration
MyRating
PlayCount
Tempo
Mood
Album_ID

Location
*****
Location
ID

Genre
*****
Genre
ID

I tried many different ways for question 1, but everytime I open up
tables Song, Location, and Artist in design mode, the data wont even
show. For question 2, I tried to use the minimum function thing, but
it does not work.
Did I create my relationship tables wrong, and if so, how should I
modify it?
How should I approach to creating the queries?

Can someone please help me, the Help thing in MS Access is so
confusing to use.
Thanks in advance.

The way your tables are designed, Norah Jones can sit in your car,
because the Artist table contains a location id.
Doesn't the location rather belong to file (song) than to an artist?
My queries are based on that change.

When opening tables in design view, you'll not see a datasheet.

To implement my queries, copy the select statements to the SQL view
of the query design window.

Query1:
SELECT tblSong.Song
FROM Location INNER JOIN (Artist INNER JOIN (Album INNER JOIN tblSong
ON Album.ID = tblSong.Album_ID) ON Artist.ID = Album.Artist_ID)
ON Location.ID = tblSong.Location_ID
WHERE ((Artist.Artist="Norah Jones") AND (Location.Location="Car"));


Query2:
SELECT TOP 1 Location.Location, Count(*) AS NoOfFiles
FROM Location INNER JOIN tblSong ON Location.ID = tblSong.Location_ID
GROUP BY Location.Location
ORDER BY Count(*);

Query3:
SELECT TOP 1 First(Artist.Artist) AS ArtistName, Count(*) AS
NoOfSongs FROM Artist INNER JOIN (Album INNER JOIN tblSong ON
Album.ID = tblSong.Album_ID) ON Artist.ID = Album.Artist_ID
GROUP BY Artist.ID
HAVING (((Artist.ID) Not In (Select TOP 1 a.ID FROM Artist AS a
INNER JOIN (Album AS al INNER JOIN tblSong AS so
ON al.ID = so.Album_ID) ON a.ID = al.Artist_ID
GROUP BY a.ID ORDER BY Count(*) DESC)))
ORDER BY Count(*);

Query4:
SELECT TOP 5 tblSong.*
FROM tblSong
ORDER BY tblSong.PlayCount DESC;

 

[germ-X]

Hello and sorry to bother again,
I don't really get query 3, because when I copy and paste it, I only get a
"ArtistName" column and a "NoOfSongs" column. The part that I dont
understand is here:
HAVING (((Artist.ID) Not In (Select TOP 1 a.ID FROM Artist AS a
INNER JOIN (Album AS al INNER JOIN tblSong AS so
ON al.ID = so.Album_ID) ON a.ID = al.Artist_ID
GROUP BY a.ID ORDER BY Count(*) DESC)))
ORDER BY Count(*);
I do not get where you get the a.ID and al.ID. Is it okay to abbreviate?
If so, how come the query won't come out right?

Also, how can I find this query using the average function? I figured out
how to pull out the top ratings for the genre, but I don't know where to put
the average function.
What is your friend’s favorite genre of music based on highest average rating?

SELECT TOP 5 Genre.Genre, tblSong.MyRating
FROM Genre, tblSong
ORDER BY tblSong.PlayCount DESC;

Thanks in advance.

 

[germ-X]

Sorry, last question:

How do I find out the top 3 favorite artists?

Thanks

 

[Wolfgang Kais]

Hello.

Query3 was the answer to "Which artist is the second-most popular
in her collection based on quantity of songs?"
"a" and "al" are aliases that I used for the tables Artist and Album.
I did so by using "Artist AS a" and "Album AS al" in the FROM clause.

The question "What is your friend's favorite genre of music based on
highest average rating?" can be answered by the following query
(provided that all data in your database describes "your friend"):

SELECT TOP 1 First(Genre.Genre) AS GenreName, Avg(tblSong.MyRating)
AS AverageRating FROM Genre INNER JOIN
(Album INNER JOIN tblSong ON Album.ID = tblSong.Album_ID)
ON Genre.ID = Album.Genre_ID
GROUP BY Album.Genre_ID
ORDER BY Avg(tblSong.MyRating) DESC;

 

[Wolfgang Kais]

You have to define a value for rating a "favorite artist".
If you mean the average rating of the songs of that artist, it was:

SELECT TOP 3 First(Artist.Artist) AS Favorites, Avg(tblSong.MyRating)
AS AverageRating FROM Artist INNER JOIN
(Album INNER JOIN tblSong ON Album.ID = tblSong.Album_ID)
ON Artist.ID = Album.Artist_ID
GROUP BY Album.Artist_ID
ORDER BY Avg(tblSong.MyRating) DESC;