Relationship stuff

[Robbvayne]

I'm trying to create a database of all my CDs, and all the tracks within. I have currently got 3 tables

Albums - containing the album title and the artist name (combined being the primary key
Artists - containg the artist nam
Tracks - containing a field called "Track Title", the artist name and album title (which are linked to the table "Albums")

I am trying to make input forms, one for putting artists in (completed successfully), a table for putting in albums for these artists (successfully completed) and a form to link track titles to the albums. I can make a form that means you can choose from ALL artist names and ALL album titles, but I want to be able to limit the album titles to the artist chosen. I think it must have to be done through some kind of looking up but I don't know how to do it. Anyone?

 

[Roger Carlson]

On my website, see sig below, is a small sample database called:
"ImplementingM2MRelationship.mdb" which illustrates how to do this.
However, I would suggest that you create autonumber primary keys for you
Albums and Artists tables. It makes the linking table easier to work with.

Also, I would remove the artist name from the album. Don't some albums have
multiple artists? Actually, since each Album can have multiple artists and
each artist can have multiple albums, you need to create a linking table
(call it AlbumArtist). THIS table should be linked to the Tracks table.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

I'm trying to create a database of all my CDs, and all the tracks within. I have currently got 3 tables:

Albums - containing the album title and the artist name (combined being the primary key)
Artists - containg the artist name
Tracks - containing a field called "Track Title", the artist name and

album title (which are linked to the table "Albums").

I am trying to make input forms, one for putting artists in (completed

successfully), a table for putting in albums for these artists (successfully
completed) and a form to link track titles to the albums. I can make a form
that means you can choose from ALL artist names and ALL album titles, but I
want to be able to limit the album titles to the artist chosen. I think it
must have to be done through some kind of looking up but I don't know how to
do it. Anyone?