3 tables - relationships come up indeterminate

[Guest]

I have 3 tables -
MusicOnPC_Artists
MusicOnPC_Songs
MusicOnPC_Files

the Artists table looks like this -
Aritst_ID (Primary Key)
Artist_Band

the Songs table looks like this -
ID (Primary Key)
Artist_ID
SongName
AlbumName
Year Published
Format
Genre
Album_ID
Notes

the Files table looks like this -
Album_ID
FileName
FileLocation
ID (Primary Key)

The Artists and Songs tables have a proper one-to-many relationship between
the Artist_ID fields. I want to place a relationship to the Files table but
no matter what I do it keeps coming up Indeterminate. I can't figure out
what's wrong. I have 3 books from the library and they don't explain what to
do in this situation.

I think I should be able to place a relationship between the Songs table and
the Files table, in the Album_ID field, it just won't work.

I know this isn't rocket science. What do I do to figure this out?

 

[rquintal]

I have 3 tables -
MusicOnPC_Artists
MusicOnPC_Songs
MusicOnPC_Files

the Artists table looks like this -
Aritst_ID (Primary Key)
Artist_Band

the Songs table looks like this -
ID (Primary Key)
Artist_ID
SongName
AlbumName
Year Published
Format
Genre
Album_ID
Notes

the Files table looks like this -
Album_ID
FileName
FileLocation
ID (Primary Key)

The Artists and Songs tables have a proper one-to-many relationship between
the Artist_ID fields. I want to place a relationship to the Files table but
no matter what I do it keeps coming up Indeterminate. I can't figure out
what's wrong. I have 3 books from the library and they don't explain what to
do in this situation.

I think I should be able to place a relationship between the Songs table and
the Files table, in the Album_ID field, it just won't work.

I know this isn't rocket science. What do I do to figure this out?

You are missing an Albums table, because album name, year published,
format should be recorded once. You probably could merge it into the
files table, since you should not have 2 or more copies of the album
on the PC. Album ID should be the Primary Key.

 

[Guest]

You are missing an Albums table, because album name, year published,
format should be recorded once. You probably could merge it into the
files table, since you should not have 2 or more copies of the album
on the PC. Album ID should be the Primary Key.

Thanks for the tip. I have made a few changes to the tables -

MusicOnPC_Artists --
Artist_ID (Primary Key) --------|
Artist_Band |
|
MusicOnPC_Albums -- |
YearPublished | One-to-Many
AlbumID (Primary Key) -----| |
Format | |
Artist_ID -----------------------|
Genre |
|
MusicOnPC_Songs -- | One-to-Many
Song_ID (Primary Key) -| |
SongName | |
Notes | |
Album_ID ------------------|
|
MusicOnPC_Files -- | One-to-One
FileName |
FileLocation |
Song_ID (Primary Key) --|
Format

I think that should work. I am just learning how to do this stuff, so if
there are any more improvements/changes please let me know.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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?

 

[Guest]

Did you base your forms on queries, or do you have combo boxes with queries
as the rowsource? Typically, when you get a prompt like this it's because you
have query somewhere that is looking for criteria. You might check any
queries you have to see if one of them has something in the criteria row of
the Album Name column.

 

[Guest]

At this point I have not created any forms or queries. I have only tested in
the Datasheet View. In that view there is a plus sign at the left of each
row, I click on the plus sign in the Albums table and the songs (from the
songs table) expand below that row (inserting themselves into more rows, a
subdatasheet). When I click on the plus sign in the Artists table it just
prompts for an album name.

The Artists table and the Albums table have a relationship set up between
the PK's, one-to-many (artists table to albums table).

BTW, I just created a query to pull artist_names from artists table and
album names from albums table and that works.

So why won't the subdatasheet work correctly?

 

[Guest]

I got it working. Here's what I found - I opened the Artists table datasheet
in Design View, opened the Properties window, and set the SubDataSheet Name
property to Auto (it was set to a specific name by default). Now the
datasheet expands out the subdatasheet properly, and everything appears to be
working fine.

Thanks for your help Sean, I really appreciate it.
Regards,
Chip W.

 

[Peter Hibbs]

Chip,

One more comment, experts in this Newsgroup recommend that you disable
the SubDataSheet Name facility once you have got everything working
OK. See this site for more info :-

http://www.granite.ab.ca/access/performancefaq.htm

HTH

Peter Hibbs.

 

[John W. Vinson]

At this point I have not created any forms or queries. I have only tested in
the Datasheet View.

That's important, and a good thing to do initially... but table datasheets are
VERY limited. Tables are designed for data storage, not for data interaction!

For a music collection application like this you will certainly need Queries
and Forms, forms with subforms. A subdatasheet lets you see *one* related
table - you can pick which one, but you can't see two. With a Form you can see
a broader and more effective view of the information, along multiple
dimensions.

John W. Vinson [MVP]

 

[Guest]

Thanks for the tips guys, much appreciated. I do plan on using forms and
queries, just using the datasheet views for testing and making sure the
links were working. I've turned off the subdatasheets and the name tracking
option as well. I also discovered another data table change to make to
streamline the dataentry, saves a lot of time with just that change.
Regards,
Chip