question from beginner...

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • haha
    #1

    question from beginner...

    I was thinking of making a music database i've created tables but I
    cannot make a good relationship out of those tables... here are the
    tables I created in access:

    CD(cdno,created on)
    Cassette(label, cassette no) <has artist(artistno ,cdno,cassette
    no, artist) <has album(albno,art istno,albumname ) <has>
    artist(.....)

    here is the problem ,

    one artist can have many album, and one album can have many artist...
    how can I show this relation in access...

    Tags: None
    • Keith Wilby
      #2
      Re: question from beginner...

      "haha" <prabesh.shrest ha@gmail.comwro te in message
      news:1157467363 .339166.323830@ m79g2000cwm.goo glegroups.com.. .
      >I was thinking of making a music database i've created tables but I
      cannot make a good relationship out of those tables
      >
      <snip>
      >
      one artist can have many album, and one album can have many artist...
      how can I show this relation in access...
      >
      One album can have many artists? Well, OK, you could try using a many to
      many relationship by using a "join" table between your artist and album
      tables.

      tblAlbum
      ID
      AlbumName
      etc ...

      tblJoin
      AlbumID
      ArtistID

      tblArtist
      ID
      ArtistName
      etc ...

      Join tblAlbum.ID to tblJoin.AlbumID in a one-to-many and join tblArtist.ID
      to tblJoin.ArtistI D in a one-to-many.

      Keith.
      https://www.keithwilby.com



        Comment

        • Craig Alexander Morrison
          #3
          Re: question from beginner...

          There are many many things to consider and it depends what you want to do.

          For instance you could have a Recording table and a RecordingReleas e table
          to deal with multiple formats LP, MC, CD, SACD, MP3, DVD-AUDIO the
          RecordingReleas e would contain data specific to the format and the Recording
          would contain data about the recording whatever format it was released in.

          It would probably be crazy to have a table for CD and one for Musicassette
          and one for......

          As to Artists and Recording/RecordingReleas e you will need to resolve the
          Many to Many relationship as per the usual approach with a table with a 1 to
          Many relationship from Artist and a 1 to Many from
          Recording/RecordingReleas e.

          It depends entirely upon the use you wish to put the database to.

          A system designed for Sony may differ to one designed for a private
          collection.


          --
          Slainte

          Craig Alexander Morrison
          Crawbridge Data (Scotland) Limited

          Small Business Solutions Provider

          "haha" <prabesh.shrest ha@gmail.comwro te in message
          news:1157467363 .339166.323830@ m79g2000cwm.goo glegroups.com.. .
          >I was thinking of making a music database i've created tables but I
          cannot make a good relationship out of those tables... here are the
          tables I created in access:
          >
          CD(cdno,created on)
          Cassette(label, cassette no) <has artist(artistno ,cdno,cassette
          no, artist) <has album(albno,art istno,albumname ) <has>
          artist(.....)
          >
          here is the problem ,
          >
          one artist can have many album, and one album can have many artist...
          how can I show this relation in access...
          >

            Comment

            • haha
              #4
              Re: question from beginner...


              Keith Wilby wrote:
              "haha" <prabesh.shrest ha@gmail.comwro te in message
              news:1157467363 .339166.323830@ m79g2000cwm.goo glegroups.com.. .
              I was thinking of making a music database i've created tables but I
              cannot make a good relationship out of those tables
              <snip>

              one artist can have many album, and one album can have many artist...
              how can I show this relation in access...
              >
              One album can have many artists? Well, OK, you could try using a many to
              many relationship by using a "join" table between your artist and album
              tables.
              >
              tblAlbum
              ID
              AlbumName
              etc ...
              >
              tblJoin
              AlbumID
              ArtistID
              >
              tblArtist
              ID
              ArtistName
              etc ...
              >
              Join tblAlbum.ID to tblJoin.AlbumID in a one-to-many and join tblArtist.ID
              to tblJoin.ArtistI D in a one-to-many.
              >
              Keith.
              www.keithwilby.com
              I did try that way... what happened was when I created a query to
              display what happens is for example:

              ARTIST - Robbie Williams
              ALBUM - NOW 40
              SONG - Kids


              ARTIST - Britney Spears
              ALBUM - NOW 40
              SONG - Kids


              ARTIST - Britney Spears
              ALBUM - NOW 40
              SONG - Lucky


              ARTIST - Britney Spears
              ALBUM - NOW 40
              SONG - Kids

              What I want is:


              ARTIST - Robbie Williams
              ALBUM - NOW 40
              SONG - Kids

              ARTIST - Britney Spears
              ALBUM - NOW 40
              SONG - Lucky

              How can I sort this out??

                Comment

                • haha
                  #5
                  Re: question from beginner...


                  Craig Alexander Morrison wrote:
                  There are many many things to consider and it depends what you want to do.
                  >
                  For instance you could have a Recording table and a RecordingReleas e table
                  to deal with multiple formats LP, MC, CD, SACD, MP3, DVD-AUDIO the
                  RecordingReleas e would contain data specific to the format and the Recording
                  would contain data about the recording whatever format it was released in.
                  >
                  It would probably be crazy to have a table for CD and one for Musicassette
                  and one for......
                  >
                  As to Artists and Recording/RecordingReleas e you will need to resolve the
                  Many to Many relationship as per the usual approach with a table with a 1 to
                  Many relationship from Artist and a 1 to Many from
                  Recording/RecordingReleas e.
                  >
                  It depends entirely upon the use you wish to put the database to.
                  >
                  A system designed for Sony may differ to one designed for a private
                  collection.
                  >
                  >
                  --
                  Slainte
                  >
                  Craig Alexander Morrison
                  Crawbridge Data (Scotland) Limited
                  >
                  Small Business Solutions Provider
                  >
                  I did think of making a different table for recording but I couldn't
                  think of not more than one attribute for that table...

                    Comment

                    Previous template Next
                    Working...