Ms-access – Need help designing a query to pull a list of songs based on ranking and the last play date

many-to-manyms access

I have a database of a list of songs used during a performance.

The main table, tblSongs, has a Field that represents a Ranking, 1 being worst, 5 being best. The table is linked to tblEvents which lists every time a song is performed. tblEvents has fields EventID and Date. The two tables are linked through tblSongsPlayed (as, obviously, it's a Many-To-Many relationship) which has two fields, SongID and EventID.

The database is in MS Access.

The part that gets complicated for me is to come up with a query that picks the songs as follows:

Show me all 5 star songs that haven't been played in the past two months AND
Show me all the 4 star songs that haven't been played in the past three months AND
Show me all the 3 star songs that haven't been played in the past five months

Wanted to respond that I see your answer and I'll check it out as soon as I can. Thank you so much!

The reason I have three tables, and maybe I'm getting this wrong here, but you can have one song played on multiple occasions, and multiple songs are played during one event. So, for example, we play SongID 1 through 5 on date 1, and then song 3 through 6 on date 2, and so forth. So it seemed to be a many-to-many relationship, a unique song can be played at multiple events.

Best Answer

From what you've shown, each part of the query has unique aspect (only 5* songs, only 4* songs, etc.).

If that's true, I'd build each query on its own, then use UNION ALL to join them together. UNION ALL takes the results of two queries (with the same number and type of columns in the SELECT list), and gives you the results of both as a single result set (add another UNION ALL to do three queries, etc.)

Any sorting necessary must be done last, after the final query, and applies to the full result set.

If separate queries for each rating are advantageous, you could potentially call each query separately. But especially in Access, UNION ALL queries are more expensive than a single query with a WHERE clause with multiple OR expressions: WHERE (Ranking = 5 AND ...) OR (Ranking = 4 AND ...).

UNION ALL queries are more useful when combing data from "incompatible" sources that cannot be done in a single query. The Access visual query designer also does not work with UNIONs.