Setlist Generation (1 Viewer)

[Pienuts]

Hello, all!

I am trying to develop a (fairly) simple DB to use with my band, and would love to automate the creation of setlists to print out for the band for when we perform.
I’m having some difficulties figuring out how to go about this. :-/

SO FAR, I’ve created a table for all the songs that we do, with these fields:

SongID (PK)– Autonumber
SongName – Text – the Name of the Song
ArtistID (FK) – Number – linked to an Artist table
Status – text - whether the song is "Learning";"Ready";"Cancelled";"To Possibly Learn"
Key – text – Key of the song
AwesomeDegree – Number - Awesomeness level of the song – 1 is great, 2 is good, 3 is okay
SetStarter – Boolean – whether it’s a good song to start a set or not
SetEnder – Boolean – whether it’s a good song to end a set or not
Encore – Boolean - whether it’s a good encore song to end a show with

TRUTHS
A setlist is comprised of 4 sets with 11 songs in each set and an encore at the end (although an option to alter this could be great for some one-off shows that we do).
We don’t play the same artist twice in one set.
We only play songs that we have ready.

WHAT I WOULD LIKE TO DO:
In a perfect world, I would like to be able to generate a setlist by:
1. Manually choosing the start and end song of each set and the encore song
2. Let Access fill in the rest of the songs, using all the best songs and the remainder as necessary (and spread them evenly throughout the show – I don’t want all the “lesser” songs in the same set)
3. Give me an option to make any changes (remove a song and replace with a different one of my choosing)
4. Save the list and print out copies for the band

A less-optimal solution would be a way to manually drag-and-drop songs into “slots” – it’s still better than what I’ve been rolling with!

If anyone has any ideas how to proceed with this, it would be much appreciated. It’s a different DB concept than I’m used to, and I can’t quite wrap my head around it!
Thanks for reading; sorry it’s so long!

 

[CJ_London]

An interesting challenge

Just to clarify, a couple of questions before suggesting some solutions:

is the setlist a single 'entity' or would you create sets which you then combine into a setlist - e.g. you have 10 sets, one setlist might use set 1,3,4,7 another might use 4,2,7,9 (plus encores)

does your set need a 'customer preference' or theme - e.g. playing at a golf club annual dinner would have a different playlist to an 18th birthday (I used to be a DJ toooo many years ago) due to different audience types. If so, can these be identified by age of music and/or perhaps the type of artist?

Any solution will require one or two tables depending on the above and again, the fields for those table will depend on the above as well.

 

[Pienuts]

Thanks for the quick reply! I thought it might pose a different challenge.

The setlist would be a single entity of four sets. I would produce a different one for each show. Because we're constantly learning new songs, I wouldn't be reusing setlists.

It's funny you mention the customer preference - I was just thinking of that. I think building it in is a great idea; I know there are a few bars in town that prefer harder stuff, and some prefer classic/oldies or modern stuff. So... yes! I think maybe I'd manually add that as a field to the song table when I populate it, as age and artist don't necessarily dictate appealability.

 

[jdraw]

Based on the thread so far, your database seems to include

Songs
Sets
SetList
Encore
Show/Event
CustomerPreference

Good luck.

 

[CJ_London]

44 songs at say avg 4 mins a song - that's about 3 hours, plus breaks so quite a long night then!

From what you have said, I would suggest your setlist table needs to look something like this

SetListPK autonumber
SetListEvent text
SetListDate date

Then you need a Set table (I would put encore here even tho it is not a set because it will be easier for data manipulation). Note Set is a reserved word, so don't use this as a table name, perhaps use setData?

SetPK autonumber
SetListFK long - links to setlist table
SetNumber integer - 1-4
SetOrder integer 1-11, plus say 12 for encore, only available on set 4
SongID long - links to song table

with regards choosing songs, the set start, end and encore (i.e. set order specified as 1, 11 or 12) are pretty straightforward based on the song table filtered by the appropriate Boolean, status and excluding artists already chosen on artistID

I would do these first for all sets + encore so you have the 'pick of the crop' for these choices.

With what remains I would determine the percentage of songs by 'awesome degree' which when combined with a random generator can then be applied evenly across each set when choosing a song (so you don't get all the good stuff in the early sets). I am sure there are more statistically relevant ways of doing it, but you will need a substantial list of available songs to get something approaching true randomness - I would think several hundred

This does not include genre (R&B, ballad, etc), theme as suggested before or time - presumable each set has a time restraint so you may need to bring that into the equation as well - I would suggest fewer/more songs to hit the time restraint rather than trying to find songs that fit the time plus all the other constraints.

 

[Pienuts]

Okay, awesome - thanks for thinking so hard on this!
I'm going to take a look tomorrow with this approach and let you know how it fares.
And yes, we play for a long time -our customers get their money's worth with us!

 

[Pienuts]

Alright, I have designed the DB mostly as per your suggestion (I messed with table names to make it easier for me to grasp), and it seems to be working the way it should! I have created a form with combo boxes to choose the start/end songs for each set as well as the encore - it's working great, and I’ve got them limiting options by songs and artists already chosen. So that’s good! I also have a command button which (so far) adds all of the chosen songs into the SetData table, and fills in their respective fields.

Now I'm a bit confused about the next step. Could you give me a better idea (maybe with the names of some VBA functions to use) of the steps necessary to auto generate the rest of the songs?

I’m also a little perplexed about being able to view and edit the setlist once it’s in the SetData table; as each song is a record how can I view and edit them in a (probably sub)form? The only way I can think to view them is by using dlookup functions in text boxes, but they wouldn’t be easily editable. I’m assuming I’m missing something obvious, but I can’t… quite… get there.

Thanks again for your help so far; I’m a lot further than I was yesterday!

 

[CJ_London]

Now I'm a bit confused about the next step.

Well done on getting this far!

I can't suggest specific functions, but within VBA suggest you need to look at recordsets, random and looping. You will also need to create a query (which will become a recordset at some point) to generate the song options.

Using the table names I suggested and assuming your form is called frmSetList I would expect an initial query along the following lines

 SELECT tblSongs.*
 FROM tblSongs LEFT JOIN tblSets ON tblSongs.SongFK=tblSets.SongPK
 WHERE (tblSets.SetListFK is null OR tblSets.SetListFK<>forms!frmSets!SetLiskFK) AND Status="Ready"

This should produce a list of songs which are ready and have not been selected for any of the sets or encore. We'll call this qryAvailable and forms your base line for available songs for the remaining slots on the set

We now have two filter requirements, Awsomeness and artistID

For awesomeness, we need to determine the percentage of these records for each degree so we can split them evenly across the sets.

so lets say there are 400 songs in this baseline set, 200 level 1, 100 level 2 and 100 level 3 - that is 50%, 25%, 25%.

At this point you need to clarify where you want to go. You have assigned slots 1and 11 for each set, so you need to assign slots 2 to 10 which is 9 songs per set, so you might say 9 * 4=36, therefore I'll only use level 1. Or you might say I'll keep it in proportion so 50% of each set will be level1, 25% level 2 etc (i.e. say 5,2,2) or anything in between, but you get the gist. For now, I suggest you build a query to do this to determine these values.

Next you'll need another query for artistPK so you don't end up with the same artist more than once in a set - this would be applied in VBA within a loop because every time you add a song to a set, you need to requery it to remove the songs by the artist of the song you have just added

For a specified set, the query for this will look something like

 SELECT *
 FROM qryAvailable 
 WHERE ArtistFK NOT IN (SELECT ArtistFK FROM tblSets INNER JOIN tblSongs ON tblSets.SongID=tblSongs.SongID WHERE tblSets.SetListFK=forms!frmSets!SetLiskFK AND tblSets.SetNumber=forms!frmSets!SetNumber)

I'm not sure of your skill levels so hesitant to go on - see if you understand this and can get the queries to work, they will interact with your form so get to this stage and we have the data we need to populate the missing slots

 

[CJ_London]

I’m also a little perplexed about being able to view and edit the setlist once it’s in the SetData table

That should not be a problem, you will be able to modify the sort order and change selected songs using a combobox with a rowsource based on the queries I've outlined

I would expect your form to be a form with a recordsource of tblSetLists and 4 subforms with a recordsource as below - one subform for each set. They could all be in one subform, but I think visually it would be better to keep them separate.

The subform recordsource (for 1st set) would be

 SELECT * from tblSets WHERE setnumber =1 ORDER BY SortOrder

and each subform control would have

linkchildfield set to SetListFK
linkmasterfield set to SetListPK

 

[Pienuts]

That's awesome, thanks so much for your help! Once again, I'll tackle it tomorrow and let you know how I fare. I have some experience with databases so I think I'm following along!

 

[CJ_London]

no problem - it's an interesting challenge

suggest when done, you post a screenshot of your relationships so I can advise using the correct table and field names, save you having to 'translate'