join error: unique index on primary table

[mysterj1]

I'm trying to create a app (access 2003) for tracking my music collection.

I've pondered over normalization and think I've got that resolved, however when creating joins between the songs and albums tables (using album_title) I get an error stating I haven't created a unique index for the referenced field of the primary table.

I've set what I consider to be the correct indexes to ensure uniqueness, what am i missing here?

Thanks in advance

 

[plog]

In general you need to use a unique autonumber as the primary key for all of your tables. In specific, here's some notes on how you have done things:

The table Artists is really serving no purpose. I would either dump it, or I would add an autonumber primary key (similar to what you have done in Genres), use that to put into Albums/Songs to use as a link. Also, divide the artists name up into First and Last.

Speaking of that autonumber ID in the Genre table--you don't use it. You should have that number in the Albums table, not the text for the genre. Always try to link tables by numbers.

Also, in the Albums table, Year_Released should be a numeric field, not text. Additionally, you need a unique primary key autonumber in this, not those 3 fields that you have designated as the primary keys.

In the Songs table, you shouldn't have the album data, because like you mentioned one song could be on multiple albums. To account for this, you will need an autonumber primary key in the Songs table and you will need to create a new table called something like AlbumSongs which will consist of 2 numeric fields--one to hold the autonumber primary key of an album and one to hold the autonumber primary key of a song.