Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2010
    Posts
    49

    Unanswered: Access Realtionship Joins - Confused

    Hey All,

    I'm trying to do a query of 3 tables and 3 junction tables so that I can set them up on a form. The problem is, I'm confused on what joins to use.

    Specifically, I have these tables:

    Performer
    SongInfo
    SoloInfo

    They're all joined in a number of ways by 3 junctions and I want to query all the song names regardless of null entries in the other tables. I'm assuming that is a left join, but what about the other 5 tables?

    Heads up, I'm not familiar with SQL language

    Cheers!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm afraid there isn't really enough information to give a qualified answer. However, my guess is that you want to get all the rows from SongInfo and any matching rows if they exist. In that case, Left Outer Join from song info to the related tables, and Left Outer Join from those to the next set of related tables. And so on.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2009
    Posts
    27
    It is correct to use Left Join, but it can be correctly decided only if i get to know the relationship between those six tables and based on the sample data

  4. #4
    Join Date
    Apr 2010
    Posts
    49
    Here's my relationship view, feel free to ask any questions; I'd be happy to explain why it is set up this way. I originally worked on the design with a smart phone developer.

    I was told (from and MS MVP) I would need to query performer, song, solo, and the relevant junctions so that I could get them on one form to enter data...correct?
    Attached Thumbnails Attached Thumbnails Capture.JPG  
    Last edited by .:RoKsTaR:.; 10-05-10 at 10:21.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    can the same song have multiple tempo's, techniques, harmonies etc.
    I could understand how say a different performer may do that, but presumably not the base song
    actually its quite possible for the same performer (whether they be solo or performer could rework the same song.. ferisntance look at the difference between standard and acoustic versions
    what is the difference between a song performer and a solo performer
    I don't see how, as you have modelled it they differ.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by .:RoKsTaR:. View Post
    Here's my relationship view, feel free to ask any questions; I'd be happy to explain why it is set up this way. I originally worked on the design with a smart phone developer.

    I was told (from and MS MVP) I would need to query performer, song, solo, and the relevant junctions so that I could get them on one form to enter data...correct?
    its one thing to be told, its another to understand what is being saud, and why

    presumably its a school work question.. no harm ion that but you need to do the work for yourself
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2010
    Posts
    49
    Haha....I'm not in school; I'm a professional musician learning data-basing to help organize teaching material Even still, I need to work out everything I read , watch, or am told. That's fine, I'm a teacher and I get that However, I worked through a number of different scenarios in joining my tables and keep getting nothing in my search results. Obviously something is wrong with my joins, but I can't figure out what


    Yes a song or solo can have multiple tempos, key changes, techniques (I have up to 11 in my combo), and harmonic idea (up to 9). Songs and solos are separate entities because what applies to one almost always doesn't apply to another. To properly teach a solo, you need to know all the technique, scales, harmonic ideas, etc. that went into it.

    Ex. You're teaching ACDC - Shook me All Night Long, which uses open chords in the key G and the solo is based in G Minor Pent 9uses bends, slides, pull offs, and hammer ons). You decide that you need to teach someone an easy song with some Pentatonic Riffs and all the other techniques listed and when you do a search this song comes up. But on further inspection you realize there aren't any little pent riffs in the actual song, just the solo. A solo which is much to hard for a beginner. That's why I need them separate, so that I can properly search songs or solos based on a set amount of criteria needed to teach
    Last edited by .:RoKsTaR:.; 10-05-10 at 11:06.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    He he! Awesome slapdown!

    Yes, it looks to me you want sings to be your sort of "root" table and every join to be a left outer, with the arrows in the designer pointing away from the song table. Make sense?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Apr 2010
    Posts
    49
    Quote Originally Posted by pootle flump View Post
    He he! Awesome slapdown!

    Yes, it looks to me you want sings to be your sort of "root" table and every join to be a left outer, with the arrows in the designer pointing away from the song table. Make sense?
    So if I lined my tables up in a row:

    Song - songperformer - performer - songsolo - soloperformer - solo

    I'd select all LO joins between each one? That seemed to easy to me originally, so I was doing combos of Left and Right and coming up with nothing. If that's the case, I guess Occam's Razor was right

    Thanks for the help, I'm sure I'll be back :P

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If I were you I'd be temtped to have an entity (table) called song
    and then have segements within that song which identify the tempo, type, and all the rest of the stuff
    ..the reason,. having done that you can define a solo as a segment and keep it all within the same tree. rather than duplicating the same stuff for song and solo, it become part of the song itself

    I think you will have probelms with your current setup of performer, if fo no other reason the performer could be a person or a group or an esemble. I thin you need to revisit that so (if required) you could workout which perfomers where part of the which group.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Apr 2010
    Posts
    49
    Except that the techniques that applies to a song, most often do not apply to a solo and i need to be able to distinguish that. Same applies to key, tempo, scales, etc. Lots of songs change key or tempo at the solo. So how do I address that? My original design had solos and songs linked, but I couldn't specify separate techniques and such for solos or accommodate songs with multiple solos.

    Thoughts?

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    heres one approach. don't think of the song as having a tempo, key or whatever, think of the song as a container for the parts of the song, and you define the tempo, harmony etc as an attribute of the segment of the song, rather than the song itself.

    another way of lookign at it
    break down the song into segments, say
    0:30s intro
    31..45s opening chord
    45..1:30 verse 1
    1:30..1:45 chorus 1
    ...
    xx:xx fade out

    this isn't the smart call if say you only ever have two keys, two tempos and so on. ie one key for the song, one key for the solo

    you can easily find all tempos for the song, so you haven't lost the ability to classify the song (just that classification happens at segment level not song level)
    you can still search on perfomers and so on

    the advantage of this type of design is that you can extend it
    say you decide to refine the segments form 'just' solos to different types of solos, you want a reprise, you want a chorus and so on.

    one thing to bear in mind this is just an opinion, only you are close to your requirements and you have to decide what you want to do what you need to represent.
    Attached Thumbnails Attached Thumbnails songsegments.png  
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Apr 2010
    Posts
    49
    Sorry do you think you could explain that whole segment thing again? I'm not sure I follow.

    does that mean I would have details for each part of the song?

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes
    but you can search for those features (say tempo) at the song level or the segment level (because the segments contain the actual data but they also identifythe song that they comprise

    terminology of segments is mine.. I'm not a musician so I dunno what you'd call it.

    what I was looking at was a way of flattening the model (so that the amount of development work to capture the information is reduced. also I was trying to remove the artificial limit on looking at songs as a whole or solo.. so you could expand itif required to look at other elements.
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Apr 2010
    Posts
    49
    I wouldn't have a song or solos table anymore, just a table designating song or solo?

    Correct?

    In your example, which table would have segment as a PK?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •