INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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:

CODE --> SQL

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

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

Matt,

Where is the table 'tblSongsPlayed' in your schema?


ATB,

Darrylle

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

(OP)
Someone pointed out that I linked to the wrong schema.

Here is the correct link.

Thanks!!


Matt

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close