If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Normalizing Table - is this right?
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? -- -------------------- http://NewsReader.Com/ -------------------- Usenet Newsgroup Service $9.95/Month 30GB |
#2
|
|||
|
|||
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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) wrote in message ... 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? -- -------------------- http://NewsReader.Com/ -------------------- Usenet Newsgroup Service $9.95/Month 30GB |
#3
|
|||
|
|||
" wrote: 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 |
#4
|
|||
|
|||
John Vinson wrote:
" wrote: 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! 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
unable to repair inobox | Sudheer Mumbai | General Discussion | 1 | February 20th, 2005 11:55 AM |
Get data from combo box to popluate the next box | Lin Light | Using Forms | 4 | December 30th, 2004 05:01 PM |
Manual line break spaces on TOC or Table of tables | Eric | Page Layout | 9 | October 29th, 2004 04:42 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Semicolon delimited text query help | Al Guerra | Running & Setting Up Queries | 3 | August 12th, 2004 11:50 AM |