Query with COUNT, but only if there's not an entry in a many-to-many relationship

[MattGreer]

I haven't been able to quite wrap my head around this.

Here's the database.


I have a query to give me a count of all the songs performed in the last XX years:

SELECT tblSongs.Title, Count(tblSongsPlayed.SongID) AS [Song Count]
FROM tblSongs INNER JOIN (tblEvents INNER JOIN tblSongsPlayed ON tblEvents.EventID = tblSongsPlayed.EventID) ON tblSongs.SongID = tblSongsPlayed.SongID
WHERE (((tblEvents.Date)>=DateAdd("yyyy",-1*[How Many Years Back Do You Want To Go?],Now())))
GROUP BY tblSongs.Title
ORDER BY Count(tblSongsPlayed.SongID) DESC;

So as you can see from the Relationships screenshot, there are themes applied to each song. There could be many. If the theme "Christmas", for example, shows up as one of the themes pertaining to a Song in tblSongs, I don't want it to appear in the query results. I've tried different things but whenever I change anything I end up losing the total Count for each song; each song gets displayed once for each time it was played which is not desired.

Best I can figure is to have two queries, one to perform the history search, and then the next to filter out songs if any has a certain "theme".

Thoughts?

Thanks!!


Matt

 

[Darrylles]

Matt,

Where is the table 'tblSongsPlayed' in your schema?


ATB,

Darrylle

 

[MattGreer]

Someone pointed out that I linked to the wrong schema.

Here is the correct link.

Thanks!!


Matt