Database to help me DJ?

[Stephenpoole4]

Essentially, I'm organizing songs in a few categories. I want to be able to search by song name and/or song key. For instance, if I search for the song "Stranger", I want to display the key for Stranger (which is the key of D) AND all other songs in my database in this key or a compatible one. For example, the song "Bounce Generation" is in the key of A. The key of A is compatible with the key of D. So I want all songs in A and D to be displayed.

Any idea on how to create these tables and their relationships?

 

[Stephenpoole4]

My name is Stephen

I'm new to the forum. I think Access is gonna solve a lot of my problem. I withdrew from an Access class while at university. I was attempting to code database material in C++ until someone on a programming forum mentioned SQL, which reminded me of Access. All my needs are personal, not business. Anyway, good to be here.

 

[bob fitz]

Re: My name is Stephen

Stephen

Please read this link about cross posting: http://www.excelguru.ca/content.php?184

Crossposted:http://www.accessforums.net/access/database-help-me-dj-55597.html

 

[Stephenpoole4]

Re: My name is Stephen

I didn't know that this website was associated with AccessForums.net when I made this post.

 

[bob fitz]

Re: My name is Stephen

I'm guessing this means you're associated with AccessForums.net, where I also posted.

Not sure what you mean by "associated with AccessForums.net". I am just a member of that forum as I am of this one.

Do all keys have a compatible key?

Do any keys have more than one compatible key?

BTW Welcome to the forum

 

[Stephenpoole4]

Re: My name is Stephen

Yes, all musical keys have 3 other compatible musical keys.

So, essentially, a song in the musical key of D corresponds to multiple songs in D and multiple songs in compatible keys (A, Bm, G). So it has a 1 to many relationship, I guess.

 

[bob fitz]

Re: My name is Stephen

Yes, all musical keys have 3 other compatible musical keys.

So, essentially, a song in the musical key of D corresponds to multiple songs in D and multiple songs in compatible keys (A, Bm, G). So it has a 1 to many relationship, I guess.

Can you give us a list of keys and their compatible keys

 

[SJB67]

I'd have thought 2 tables should do it.

Table 1 with your song details: Name, artist, key etc
Table 2 associating the 3 relevant keys together, eg. A, D & E

You can then search by key, and those related keys.

You can obviously expand further with genres etc which you would probably keep in a separate table, and depending on size and usage perhaps artists on their own table with further details about each.

Hope this makes sense

 

[bob fitz]

I'd have thought 2 tables should do it.

Table 1 with your song details: Name, artist, key etc
Table 2 associating the 3 relevant keys together, eg. A, D & E

You can then search by key, and those related keys.

You can obviously expand further with genres etc which you would probably keep in a separate table, and depending on size and usage perhaps artists on their own table with further details about each.

Hope this makes sense

I could be wrong but I think 3 tables will be required.

Table 1 with song titles etc
Table 2 with Keys
Table 3 with fields for Key from Table 2 and a field for a compatible key

 

[AnthonyGerrard]

Re: My name is Stephen

Saw the film once, a very sad tale, with a tragic after film real life end too.

 

[bob fitz]

Re: My name is Stephen

Saw the film once, a very sad tale, with a tragic after film real life end too.

I don't understand the relevance of this to the current discussion .

 

[Stephenpoole4]

Re: My name is Stephen

Can you give us a list of keys and their compatible keys

Yes,
C is harmonic with F, G, and Am
C#is harmonic with F#, G#, A#/Bbm
D is harmonic with G, A, Bm
D# is harmonic with G#, A#, Cm
E is harmonic with A, B, C#
F is harmonic with A#, C, Dm
G is harmonic with C, D, Em
G# is harmonic with D#, C#, Fm
A is harmonic with D, E, F#m
A# is harmonic with D#, F, G

Then there's a bunch of minor keys too, but I don't want to overwhelm you.

 

[Stephenpoole4]

I'd have thought 2 tables should do it.

Table 1 with your song details: Name, artist, key etc
Table 2 associating the 3 relevant keys together, eg. A, D & E

You can then search by key, and those related keys.

You can obviously expand further with genres etc which you would probably keep in a separate table, and depending on size and usage perhaps artists on their own table with further details about each.

Hope this makes sense

Can you explain this? I want to be able to search and have all relevant results displayed. How can I create relationships with the harmonic keys in the second table?

For instance, the song "Bounce Generation" is in the harmonic key of A. How can I structure the table/relationships so Bounce Generation will be displayed?

 

[SJB67]

tblHarmonics has a primary key of 'Key' this has a one to many relationship with the song key in tblSongs

on a search form you select your key which will return its harmonics you can then query your tblSongs for all songs that are in any of the 3 keys.

let me know if you need more detail

 

[bob fitz]

Re: My name is Stephen

If I have understood your requirement correctly, the attached db may be of interest.

 

[pbaldy]

Re: My name is Stephen

FYI, thread moved out of introduction forum. Not sure why the op was flagged as a cross post, since it was just an introduction.

 

[bob fitz]

Re: My name is Stephen

FYI, thread moved out of introduction forum. Not sure why the op was flagged as a cross post, since it was just an introduction.

Thanks for moving the thread. The confusion regarding cross posting is because I was originally replying to the OP's post: http://www.access-programmers.co.uk/forums/showthread.php?t=281461 but posted my reply in the wrong thread.

 

[pbaldy]

Re: My name is Stephen

Should I merge the threads?

 

[bob fitz]

Re: My name is Stephen

Should I merge the threads?

IMHO that would probably make things clearer for other viewers but as you're a moderator here I'm happy to leave that decision to you Paul. Besides, I've had a couple of glasses of wine now so my thinking may be a bit clouded right now

 

[pbaldy]

Threads merged, and the wino moderator is not happy that you're already having wine when I'm about to have breakfast.