Normalizing Table - is this right?

[abqhusker]

Hi. I have a flat table of all my cd and mp3 albums. Fields are Artist,
Album, Size, Format, and CDR Number. Currently no primary key. I am
thinking that I should make Artist filed a primary key but don't know how
to do it without losing the other records for albums. I want to normalized
this data to where the Artist field is primary key and will not contain
duplicate entries of the same artist but still maintain the relationship to
all a particular artist's albums. Currently the table has several records
with same artist name in Artist field. Any recommendations or is this
table OK the way it is?

 

[Douglas J. Steele]

You need a second table to contain one row for each Artist. That table
should have something like ArtistID as its primary key, and Artist as
another field. Change your existing table so that it has ArtistID as a
foreign key pointing back to the new table.

 

[John Vinson]

Hi. I have a flat table of all my cd and mp3 albums. Fields are Artist,
Album, Size, Format, and CDR Number. Currently no primary key. I am
thinking that I should make Artist filed a primary key but don't know how
to do it without losing the other records for albums. I want to normalized
this data to where the Artist field is primary key and will not contain
duplicate entries of the same artist but still maintain the relationship to
all a particular artist's albums. Currently the table has several records
with same artist name in Artist field. Any recommendations or is this
table OK the way it is?

Do follow Douglas' advice about the separate table. Making ArtistID the
Primary Key of your current table would require you to remove all but the
first work of each artist, since the Primary Key is by definition unique!

Once you create your new Artist table with the ArtistID primary key, you can
run two queries in succession: first create a Query selecting only the artist
name from your CD table; set the query's Unique Values property to Yes;
change it to an Append query, and append the artists' names to the Artist
table.

Then add an ArtistID field to your CD's table (use a Long Integer if the
ArtistID in Artists is an autonumber); create an Update Query joining the
newly filled Artists table to the CD table, joining *by the artist's name*.
Update the foreign key field in CDs to [Artists].[ArtistID].

John W. Vinson/MVP

 

[abqhusker]

:




Do follow Douglas' advice about the separate table. Making ArtistID the
Primary Key of your current table would require you to remove all but the
first work of each artist, since the Primary Key is by definition unique!

OK, I was able to do this, I have a table now with just one occurence of
each artist and primary key is autonumber, only two fields in Artist Table.

Once you create your new Artist table with the ArtistID primary key, you can
run two queries in succession: first create a Query selecting only the artist
name from your CD table; set the query's Unique Values property to Yes;
change it to an Append query, and append the artists' names to the Artist
table.

I don't know how to "set query's Unique Values property to Yes". I'm
not sure I'm doing the append query right either. I hate to
inconvenience any one here in this group, but is there anyway/anywhere I
can get dummy step by step instructions to run the two queries you are
recommending I do? It's all a little confusing to me still.

Then add an ArtistID field to your CD's table (use a Long Integer if the
ArtistID in Artists is an autonumber); create an Update Query joining the
newly filled Artists table to the CD table, joining *by the artist's name*.
Update the foreign key field in CDs to [Artists].[ArtistID].

John W. Vinson/MVP

Thank you very much for answering, John and Doug. I believe you
understand exactly what I'm trying to do and it's obvious I just don't
know enough to do it without being handheld. Can you recommend some
website or tutorial that will help me learn how to do these things? I
guess where I'm confused is why do I append artist names from the cd
list table to the Artist table when it already has the Artist names...do
I make another field/column on the Artist table and have 2 Artist fields
that are exactly the same? Is the ArtistID field in the main CDtable a
foreign key field?

I really want to learn how to fix this cd list database by normalizing
it with PKs, and foreign keys (dunno what those are) because I think it
will give me a better understanding of the concepts of access. My
knowledge is strictly beginner but I really want to be good at this
program. Thanks again for all your help, John.

Ed Terlaje