microsoft.public.access

Very well, so far so good with one exception - in Datasheet View the artists
table is not linking to the albums table. When i click on the plus sign at
the left of each row I get a prompt to enter an album name. I should be
getting the list of albums related to that artist.

I have the relationship set up - artist table (one side) -> album table
(many side) via the Artist_ID field, which is the same in both tables. It is
autonumber in the artist table and number in the albums table. All other
tables link just fine. I've deleted and recreated the relationship, but still
it does not work.

Any ideas what I should check?
--
chip

"Beetle" wrote:

> Yes that's correct if that's how YOU want it to be (sorry, I should have been
> more clear about that in my post). Perhaps you always limit an artist to
> belonging to only one genre (I don't know), in which case you would put
> GenreID in the artists table (as a Foreign Key to the Genres table) and you
> would just have a One-to-Many relationship between Genre/Artist.
>
> If you don't want to limit an artist to only one genre, then it *seems* to
> me that the best way to link the Artist/Genre would be through the albums
> table. In other words, an artist wouldn't belong directly to a genre, only
> their albums would. I've never actually created a music DB before, so this is
> just my opinion (for what it's worth). It all depends on what your
> preferences are.
>
> HTH
> --
> _________
>
> Sean Bailey
>
>
> "Chip" wrote:
>
> > You mention in your last paragraph - "This structure assumes a Many-to-Many
> > relationship between Artists and Genres (a genre can have many artists and an
> > artist can be in more than one genre)."
> >
> > If I have table Artists (one side) -> albums table (many side)
> > and also table Genre (one side) -> albums table (many side)
> >
> > does that equal a many-to-many relationship between Artists and Genres?
> > That's the only part I am finding a bit confusing right now.
> >
> >
> > Thanks for your help, I really appreciate it.
> >
> > --
> > chip
> >
> > I have -
> >
> > artist table -
> > Artist_ID (PK related to Aritst_ID on albums table) (one side)
> > Aritsts
> >
> > albums table -
> > Album_ID (PK) (related to Album_ID on songs table) (one side)
> > GenreID (FK to Genre table) (many side)
> > Artist_ID (FK from artist table) (many side)
> > +others
> >
> > Genre table -
> > GenreID (PK related to GenreID on albums table) (one-to-many)
> > GenreName
> >
> > Songs table -
> > Song_ID (PK)
> > Album_ID (FK to Albums table) (many side)
> > FileID (FK to Files table) (many side)
> >
> > Files table -
> > FileID (PK related to Songs table) (one side)
> > FileLocation
> >
> >
> > "Beetle" wrote:
> >
> > > If you have music from many different genres, then you may want to add a
> > > table for that also. Maybe something like the following;
> > >
> > > tblArtists
> > > *******
> > > ArtistID (Primary Key)
> > > ArtistName
> > >
> > > tblAlbums
> > > *******
> > > AlbumID (PK)
> > > ArtistID (Foreign Key to tblArtists)
> > > GenreID (Foreign Key to tblGenres)
> > > AlbumName
> > > YearPublished
> > > Format
> > >
> > > tblGenres
> > > *******
> > > GenreID (PK)
> > > GenreName
> > >
> > > tblSongs
> > > *******
> > > SongID (PK)
> > > AlbumID (FK to tblAlbums)
> > > FileID (FK to tblFiles)
> > > SongName
> > > Notes
> > >
> > > tblFiles
> > > *****
> > > FileID (PK)
> > > FileLocation
> > >
> > > This structure assumes a Many-to-Many relationship between Artists and
> > > Genres (a genre can have many artists and an artist can be in more than one
> > > genre). The relationship between the artist and the genre is defined by
> > > tblAlbums. You then have One-to-Many relatonships between Albums/Songs and
> > > Files/Songs.
> > >
> > > HTH
> >