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

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.

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.

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]

"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