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.