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 |
#11
|
|||
|
|||
PartNumber can be used as primary key
HaLevi wrote:
I have tried a number of times to add an auto number column to a table and every time it says you cannot because there is already data in the table. If you know a way to do it anyway , please tell me. You would get that error if you tried to change an existing number field to an AutoNumber, but adding a NEW AutoNumber field should be no problem. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#12
|
|||
|
|||
PartNumber can be used as primary key
With your table in design view, insert a row at the top of the design
form. Give the field the name of your table followed by "ID": MyTableID. For type choose Autonumber. With that row/field still selected, click the Key icon on the toolbar above the window. That makes MyTableID the Primary Key of this table. Save your table return to table view. You will find a value in every row of the field MyTableID. As for the value of an autonumber datatype, its ONLY purpose in life is to generate unique values to serve as a surrogate Primary Key. Any other use of the autonumber datatype will eventually lead you into trouble. It is not guaranteed to be sequential. The content of that field should never be seen nor used by a human being. If it is shown troubles will come. An earlier responder obviously prefers to use natural keys. That's fine people should do what they feel to be best. The problem is that the arguments he put forward don't address the real issues.: ================================================== == "I like my PKs to have some relationship to the data. Something like a SSN, or a company-generated PO # or Invoice #." SSNs are poor primary keys. They can be changed. So can every other single thing in a record including a person's name. This from a fellow with the scars of battles fought, some won and some lost. I've been promised on the life of first born children that a certen element of data "will never change". HAH! They never let me down, the promised unchangeable element always changed. Company-generated PO#s can be altered after the fact due to human error. I solved the problem to my satisfaction by always using autonumber Primary Keys. Never again did I have to sweat the primary keys nor waste the time formerly spent on resolving issues thereto. "Autonumber fields have nothing to do with our data and are more difficult to work with than a field of our own choosing. Yes, there's more chance of error when the user is expected to enter the PK manually but in practice we see no big source of error here. The PK actually makes sense to the user so the user is more likely to enter it correctly." Absolutely! Right On! Autonumber fields have absolutely nothing to do with anyone's data and that is their greatest value because they're immune to all changes to do with the user's data. Further, if you never show it to your users, no one will ever be after you to change it. Google these groups for years past and you'll see lots of posts seeking help in managing autonumber values. In some cases it's just that the programmer wants things to appear all neat and tidy (that's really worrying about neat and tidy in the wrong places, expend your efforts on the application because that's what you get paid to do). However, in many cases, some control freak boss has seen the "ID" label on a control and insists that the programmer jump through hoops and make the autonumber behave per the control freak's whim of the day. IMHO, if you are using Autonumber primary keys and you require a user to enter that autonumber value **for any reason whatever**, your application is screwed up! By their very definition, autonumbers are generated by Access. When that autonumber is referenced in a Foreign Key, Referential Integrity takes care of it for you. ================================================== ==== If anyone is boxed in a corner and causing her or his users to enter autonumber values manually to make an application work, please post back into these groups expressing your problem and asking for help resolving it. That's what these newsgroups are for. HTH -- -Larry- -- "HaLevi" wrote in message ... I have tried a number of times to add an auto number column to a table and every time it says you cannot because there is already data in the table. If you know a way to do it anyway , please tell me. -- knuble07 "Douglas J Steele" wrote: What do you mean by adding an autonumber to a table? Are you talking about adding a new field to the table and making that field an Autonumber field, or do you mean assigning the Autonumber field a value? There shouldn't be any problem adding a new Autonumber field to an existing table. You shouldn't be trying to assign values to Autonumber fields. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "HaLevi" wrote in message ... Dear Mr. Spencer, How do you add autonumber to a table that already has data in it. When I try I am denied for the very reason that there is data? -- knuble07 "John Spencer" wrote: An autonumber in Access is automatically assigned when a record is created and even if the record is not saved, the number is used up. The number will be unique for the entire table. It cannot be edited. You can add an autonumber field to a table at any time. I do not know what algorithm the engine uses to decide which record gets which number - I would suspect that the numbers are assigned in record storage order, but that is just a guess. Autonumbers are NUMBERS and therefore don't contain (and can't) contain any letters. Autonumbers often make good primary keys since they are automatically generated, unique, and stable. However, they aren't good things to show to the users since they often have little direct relationship to the data that is stored in the row. They are a convenience in establishing and maintaining relationships between tables. "HaLevi" wrote in message ... What are the rules I guess - of the auto assigned numbers in Access? I have read that once you don't have it in place when you start a database it is too late to add it after. So if I want it used in a database that didn't have it turned on, can I just start a new database and then move that info in? Also, can I edit Autonumber? If I can't then - maybe I wasn't clear before - then I cannot add the letters in front of it. In which case, I want to have Autonumber and then copy the auto assigned number for each new book automatically into a copy of that in a different column which we'll call Item Number. Thank you -- knuble07 "John Spencer" wrote: Is PartNumber always unique? If you use two (or more) manufacturers or suppliers will the part numbers still be unique? Is part number stable (that is, it won't change over time)? If the answers are yes for YOUR database then using PartNumber as the primary key is probably a good idea. It will save space, make your queries simpler since you often will need only the PartNumber and therefore won't need to add the Parts table to your queries. "Kyle" wrote in message ... When setting up a table, can I use actual PartNumber as a primary key instead of assign one as PartNumberID with data type as autonumber. I would like to use actual PartNumber as a primary key. But.. which way is better? Any suggestion or recommend. Thanks. |
#13
|
|||
|
|||
PartNumber can be used as primary key
Thanks to all who answered, especially Larry - IT WORKED!
-- knuble07 "Larry Daugherty" wrote: With your table in design view, insert a row at the top of the design form. Give the field the name of your table followed by "ID": MyTableID. For type choose Autonumber. With that row/field still selected, click the Key icon on the toolbar above the window. That makes MyTableID the Primary Key of this table. Save your table return to table view. You will find a value in every row of the field MyTableID. As for the value of an autonumber datatype, its ONLY purpose in life is to generate unique values to serve as a surrogate Primary Key. Any other use of the autonumber datatype will eventually lead you into trouble. It is not guaranteed to be sequential. The content of that field should never be seen nor used by a human being. If it is shown troubles will come. An earlier responder obviously prefers to use natural keys. That's fine people should do what they feel to be best. The problem is that the arguments he put forward don't address the real issues.: ================================================== == "I like my PKs to have some relationship to the data. Something like a SSN, or a company-generated PO # or Invoice #." SSNs are poor primary keys. They can be changed. So can every other single thing in a record including a person's name. This from a fellow with the scars of battles fought, some won and some lost. I've been promised on the life of first born children that a certen element of data "will never change". HAH! They never let me down, the promised unchangeable element always changed. Company-generated PO#s can be altered after the fact due to human error. I solved the problem to my satisfaction by always using autonumber Primary Keys. Never again did I have to sweat the primary keys nor waste the time formerly spent on resolving issues thereto. "Autonumber fields have nothing to do with our data and are more difficult to work with than a field of our own choosing. Yes, there's more chance of error when the user is expected to enter the PK manually but in practice we see no big source of error here. The PK actually makes sense to the user so the user is more likely to enter it correctly." Absolutely! Right On! Autonumber fields have absolutely nothing to do with anyone's data and that is their greatest value because they're immune to all changes to do with the user's data. Further, if you never show it to your users, no one will ever be after you to change it. Google these groups for years past and you'll see lots of posts seeking help in managing autonumber values. In some cases it's just that the programmer wants things to appear all neat and tidy (that's really worrying about neat and tidy in the wrong places, expend your efforts on the application because that's what you get paid to do). However, in many cases, some control freak boss has seen the "ID" label on a control and insists that the programmer jump through hoops and make the autonumber behave per the control freak's whim of the day. IMHO, if you are using Autonumber primary keys and you require a user to enter that autonumber value **for any reason whatever**, your application is screwed up! By their very definition, autonumbers are generated by Access. When that autonumber is referenced in a Foreign Key, Referential Integrity takes care of it for you. ================================================== ==== If anyone is boxed in a corner and causing her or his users to enter autonumber values manually to make an application work, please post back into these groups expressing your problem and asking for help resolving it. That's what these newsgroups are for. HTH -- -Larry- -- "HaLevi" wrote in message ... I have tried a number of times to add an auto number column to a table and every time it says you cannot because there is already data in the table. If you know a way to do it anyway , please tell me. -- knuble07 "Douglas J Steele" wrote: What do you mean by adding an autonumber to a table? Are you talking about adding a new field to the table and making that field an Autonumber field, or do you mean assigning the Autonumber field a value? There shouldn't be any problem adding a new Autonumber field to an existing table. You shouldn't be trying to assign values to Autonumber fields. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "HaLevi" wrote in message ... Dear Mr. Spencer, How do you add autonumber to a table that already has data in it. When I try I am denied for the very reason that there is data? -- knuble07 "John Spencer" wrote: An autonumber in Access is automatically assigned when a record is created and even if the record is not saved, the number is used up. The number will be unique for the entire table. It cannot be edited. You can add an autonumber field to a table at any time. I do not know what algorithm the engine uses to decide which record gets which number - I would suspect that the numbers are assigned in record storage order, but that is just a guess. Autonumbers are NUMBERS and therefore don't contain (and can't) contain any letters. Autonumbers often make good primary keys since they are automatically generated, unique, and stable. However, they aren't good things to show to the users since they often have little direct relationship to the data that is stored in the row. They are a convenience in establishing and maintaining relationships between tables. "HaLevi" wrote in message ... What are the rules I guess - of the auto assigned numbers in Access? I have read that once you don't have it in place when you start a database it is too late to add it after. So if I want it used in a database that didn't have it turned on, can I just start a new database and then move that info in? Also, can I edit Autonumber? If I can't then - maybe I wasn't clear before - then I cannot add the letters in front of it. In which case, I want to have Autonumber and then copy the auto assigned number for each new book automatically into a copy of that in a different column which we'll call Item Number. Thank you -- knuble07 "John Spencer" wrote: Is PartNumber always unique? If you use two (or more) manufacturers or suppliers will the part numbers still be unique? Is part number stable (that is, it won't change over time)? If the answers are yes for YOUR database then using PartNumber as the primary key is probably a good idea. It will save space, make your queries simpler since you often will need only the PartNumber and therefore won't need to add the Parts table to your queries. "Kyle" wrote in message ... When setting up a table, can I use actual PartNumber as a primary key instead of assign one as PartNumberID with data type as autonumber. I would like to use actual PartNumber as a primary key. But.. which way is better? Any suggestion or recommend. Thanks. |
#14
|
|||
|
|||
PartNumber can be used as primary key
On Tue, 21 Feb 2006 09:52:59 -0800, HaLevi
wrote: (Meaning: I sell books. I don't want to have to manually assign thousands of different numbers to give part numbers for each of my listings. So if I use the automatically assigned number for the record as the part number, and then possibly add a few letters before the given number if necessary that will tell me what category of book the given part number is in - would that work? - Hope I am clear...) Well... that is NOT a good idea. A field should have one purpose ONLY. Storing both a part number and a category in one field is a violation of this principle. Stoer the part number in one field; and store the category in a different field. John W. Vinson[MVP] |
#15
|
|||
|
|||
PartNumber can be used as primary key
"John Vinson" wrote in message
... On Tue, 21 Feb 2006 09:52:59 -0800, HaLevi wrote: (Meaning: I sell books. I don't want to have to manually assign thousands of different numbers to give part numbers for each of my listings. So if I use the automatically assigned number for the record as the part number, and then possibly add a few letters before the given number if necessary that will tell me what category of book the given part number is in - would that work? - Hope I am clear...) Well... that is NOT a good idea. A field should have one purpose ONLY. Storing both a part number and a category in one field is a violation of this principle. Stoer the part number in one field; and store the category in a different field. He can then use both fields as an aggregate PK. Tom Lake |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Autonumber using alpanumerics | Katharine Jansen | Database Design | 18 | August 19th, 2005 12:54 PM |
Table Design | A. Williams | Database Design | 3 | April 29th, 2005 07:02 PM |
COMBOBOX - RECORDS IN TABLE | Samora | New Users | 5 | March 3rd, 2005 01:41 PM |
Are three primary keys less effecient than two? | Dale | Database Design | 4 | October 5th, 2004 05:33 AM |
Muliple primary key | Billy K | Database Design | 4 | May 31st, 2004 02:50 PM |