AutoNumber Question

Good point. But does this mean that setting the No Duplicates option on a
particular field only applies if that field is a Primary Key? I was under
the impression that it you can select No Dups for any Field. So, with the
SSN or VIN anaolgies, couldnt one still ensure the uniqueness of them, even
if they werent the PriKey? And still use the AutoNumber option for the
Primary?



"BruceM" wrote in message
...
I would like to point out that a primary key does not need to be
meaningless to the user. A sequential number such as 000001, 000002, etc.
may be used for invoice numbers or the like. I don't see any reason why
that number can't be the PK.
There are legal reasons why SSN should not be widely available in a
database, as it would be if it was part of relationships. However, I
would think an employer would want to know if the number is not unique.
If John Smith leaves the company, and five years later Jill Jones shows up
with John Smith's SSN, I expect it would be a real mess for a company that
simply ignores the duplication.
A VIN as a PK could get a bit murky for an insurance company or the DMV if
a vehicle changes hands, but I expect either entity would want to know if
a VIN is being reused. A VIN could make sense as a PK in some situations.
Uniqueness is one thing; suitablility for use as a PK is another. There
are a lot of possible circumstances, each of which needs to be considered
separately.

"accessquestions" wrote in message
...
understood. I actually thought of that after I posted.
Just goes to show that it really takes an analytical mind to perfect the
concepts of relational databases.
A VIN, like you state, serves a purpose, and is an attribute (an
important one) of a vehicle. Not just a random number to identify it's
uniqueness in a database. If it were a Database containing vehicle
information, the VIN would not be a proper number to use a primary key
identifier either (just like a SSN), but instead a value relating to
another value. And even then, a random number, such as Aceess'
AutoNumber should be used for the system to identify it and its
uniqueness.
This dialogue was very educational for me as it relates to what I have
read many times on the boards ut it all on paper first", and,
"determine all information to be stored in the database and its relations
to other tables" before creating them. If this was done, I would know
that the SSN and VIN Numbers were not suitable candidates for a Unique
Identifier.
Point Taken !
Thanks for all your insight ,as usual

VL


"tina" wrote in message
news
well, glad i could help.

but i have to point out that the VIN analogy is not apt. a VIN is not
just a
collections of numbers/letters, it is a very specific code that
identifies
manufacturer, make and model of vehicle, model year of vehicle,
sometimes
where the vehicle was built, sometimes engine size, sometimes maximum
payload for trucks, and other data - as well as the last 4 to 6 digits
that
are unique to a specific vehicle that is otherwise identical to many
other
vehicles in all those other respects. so a VIN has meaning separate from
any
tracking database that stores it, and it must often be seen and used
directly by people - such as manufacturers, owners, insurers, DMVs.

an Autonumber primary key, on the other hand, has no meaning outside of
the
database where the data is stored, and no meaning within the database,
except to the software - and it absolutely should *not* be used with the
idea of attaching a meaning to it. it also should not be seen by anyone
who
uses the database, at any time - excepting only that the developer may
look
at Autonumber values from time to time while building and/or
troubleshooting
the database.

hth


"accessquestions" wrote in message
...
good point.
the Auto Number is sort like a VIN on a car, you rarely have to see it,
but
its there and its serving a purpose,, and nobody committs it to
memory,
if
they would it would just confuse them. you convinced me, im an auto
number
guy from now on.
thanks


"tina" wrote in message
...
well, personally, i would not use the SSN as a primary key, for 2
reasons.
first, SSNs are unfortunately not unique; there are many, many
duplicates
floating around out there. of course, only one person is using a
specific
SSN *legally*, but unless you want to get into tracking down
fraudulent
use... second, if you use the SSN as primary key, you'll have it
populating
multiple tables, potentially scattered all over your database. again,
personally, i would safeguard an SSN the same way i would a credit
card
number, in my database; put it in one place, and one place only, so
it's
easier to protect by whatever means you use to secure proprietary
data.

having said all that, there's no reason you can't use an Autonumber
as
the
primary key field for your employee table *as long as it is a value
the
has
no meaning to anyone, only to Access*. your user should never see an
Autonumber primary key, or use it directly to search for a record in
the
table. don't confuse how your *user* finds a certain record in a
table
with
how *Access* identifies each record in a table. you can easily use an
Autonumber primary key and still allow your user to seach for an
employee
record using the person's SSN.

hth


"accessquestions" wrote in message
...
Hello,
As far as a Primary Key is concerned (for an Employee Table)
I have read numerous times on the boards NOT to use the Auto Number
feature,
I'm not sure exactly why, although I could see where the random
number
can
confuse some.
Would anyone recommend using a SSN instead? It will be unique, and
it
will
serve as a good reference to the employee.
Any thoughts of why or why not?

Thanks Again !

VL

comments inline.

"accessquestions" wrote in message
...
Good point. But does this mean that setting the No Duplicates option on a
particular field only applies if that field is a Primary Key?

no.

I was under
the impression that it you can select No Dups for any Field.

that is correct.

So, with the
SSN or VIN anaolgies, couldnt one still ensure the uniqueness of them,
even
if they werent the PriKey?

well, to be more precise, you can set a unique index on any field to ensure
that no duplicate values can be entered in the field. you may have duplicate
*data*, but you can't enter that duplicate data in a field with a Unique
index setting.

And still use the AutoNumber option for the
Primary?

correct.




"BruceM" wrote in message
...
I would like to point out that a primary key does not need to be
meaningless to the user. A sequential number such as 000001, 000002,
etc.
may be used for invoice numbers or the like. I don't see any reason why
that number can't be the PK.
There are legal reasons why SSN should not be widely available in a
database, as it would be if it was part of relationships. However, I
would think an employer would want to know if the number is not unique.
If John Smith leaves the company, and five years later Jill Jones shows
up
with John Smith's SSN, I expect it would be a real mess for a company
that
simply ignores the duplication.
A VIN as a PK could get a bit murky for an insurance company or the DMV
if
a vehicle changes hands, but I expect either entity would want to know
if
a VIN is being reused. A VIN could make sense as a PK in some
situations.
Uniqueness is one thing; suitablility for use as a PK is another. There
are a lot of possible circumstances, each of which needs to be
considered
separately.

"accessquestions" wrote in message
...
understood. I actually thought of that after I posted.
Just goes to show that it really takes an analytical mind to perfect
the
concepts of relational databases.
A VIN, like you state, serves a purpose, and is an attribute (an
important one) of a vehicle. Not just a random number to identify it's
uniqueness in a database. If it were a Database containing vehicle
information, the VIN would not be a proper number to use a primary key
identifier either (just like a SSN), but instead a value relating to
another value. And even then, a random number, such as Aceess'
AutoNumber should be used for the system to identify it and its
uniqueness.
This dialogue was very educational for me as it relates to what I have
read many times on the boards ut it all on paper first", and,
"determine all information to be stored in the database and its
relations
to other tables" before creating them. If this was done, I would know
that the SSN and VIN Numbers were not suitable candidates for a Unique
Identifier.
Point Taken !
Thanks for all your insight ,as usual

VL


"tina" wrote in message
news well, glad i could help.

but i have to point out that the VIN analogy is not apt. a VIN is not
just a
collections of numbers/letters, it is a very specific code that
identifies
manufacturer, make and model of vehicle, model year of vehicle,
sometimes
where the vehicle was built, sometimes engine size, sometimes maximum
payload for trucks, and other data - as well as the last 4 to 6 digits
that
are unique to a specific vehicle that is otherwise identical to many
other
vehicles in all those other respects. so a VIN has meaning separate
from
any
tracking database that stores it, and it must often be seen and used
directly by people - such as manufacturers, owners, insurers, DMVs.

an Autonumber primary key, on the other hand, has no meaning outside
of
the
database where the data is stored, and no meaning within the database,
except to the software - and it absolutely should *not* be used with
the
idea of attaching a meaning to it. it also should not be seen by
anyone
who
uses the database, at any time - excepting only that the developer may
look
at Autonumber values from time to time while building and/or
troubleshooting
the database.

hth


"accessquestions" wrote in message
...
good point.
the Auto Number is sort like a VIN on a car, you rarely have to see
it,
but
its there and its serving a purpose,, and nobody committs it to
memory,
if
they would it would just confuse them. you convinced me, im an auto
number
guy from now on.
thanks


"tina" wrote in message
...
well, personally, i would not use the SSN as a primary key, for 2
reasons.
first, SSNs are unfortunately not unique; there are many, many
duplicates
floating around out there. of course, only one person is using a
specific
SSN *legally*, but unless you want to get into tracking down
fraudulent
use... second, if you use the SSN as primary key, you'll have it
populating
multiple tables, potentially scattered all over your database.
again,
personally, i would safeguard an SSN the same way i would a credit
card
number, in my database; put it in one place, and one place only, so
it's
easier to protect by whatever means you use to secure proprietary
data.

having said all that, there's no reason you can't use an Autonumber
as
the
primary key field for your employee table *as long as it is a value
the
has
no meaning to anyone, only to Access*. your user should never see
an
Autonumber primary key, or use it directly to search for a record
in
the
table. don't confuse how your *user* finds a certain record in a
table
with
how *Access* identifies each record in a table. you can easily use
an
Autonumber primary key and still allow your user to seach for an
employee
record using the person's SSN.

hth


"accessquestions" wrote in message
...
Hello,
As far as a Primary Key is concerned (for an Employee Table)
I have read numerous times on the boards NOT to use the Auto
Number
feature,
I'm not sure exactly why, although I could see where the random
number
can
confuse some.
Would anyone recommend using a SSN instead? It will be unique,
and
it
will
serve as a good reference to the employee.
Any thoughts of why or why not?

Thanks Again !

VL

alrighty then,
thanks Tina!



"tina" wrote in message
...
comments inline.

"accessquestions" wrote in message
...
Good point. But does this mean that setting the No Duplicates option on
a
particular field only applies if that field is a Primary Key?

no.

I was under
the impression that it you can select No Dups for any Field.

that is correct.

So, with the
SSN or VIN anaolgies, couldnt one still ensure the uniqueness of them,
even
if they werent the PriKey?

well, to be more precise, you can set a unique index on any field to
ensure
that no duplicate values can be entered in the field. you may have
duplicate
*data*, but you can't enter that duplicate data in a field with a Unique
index setting.

And still use the AutoNumber option for the
Primary?

correct.




"BruceM" wrote in message
...
I would like to point out that a primary key does not need to be
meaningless to the user. A sequential number such as 000001, 000002,
etc.
may be used for invoice numbers or the like. I don't see any reason why
that number can't be the PK.
There are legal reasons why SSN should not be widely available in a
database, as it would be if it was part of relationships. However, I
would think an employer would want to know if the number is not unique.
If John Smith leaves the company, and five years later Jill Jones shows
up
with John Smith's SSN, I expect it would be a real mess for a company
that
simply ignores the duplication.
A VIN as a PK could get a bit murky for an insurance company or the DMV
if
a vehicle changes hands, but I expect either entity would want to know
if
a VIN is being reused. A VIN could make sense as a PK in some
situations.
Uniqueness is one thing; suitablility for use as a PK is another.
There
are a lot of possible circumstances, each of which needs to be
considered
separately.

"accessquestions" wrote in message
...
understood. I actually thought of that after I posted.
Just goes to show that it really takes an analytical mind to perfect
the
concepts of relational databases.
A VIN, like you state, serves a purpose, and is an attribute (an
important one) of a vehicle. Not just a random number to identify
it's
uniqueness in a database. If it were a Database containing vehicle
information, the VIN would not be a proper number to use a primary key
identifier either (just like a SSN), but instead a value relating to
another value. And even then, a random number, such as Aceess'
AutoNumber should be used for the system to identify it and its
uniqueness.
This dialogue was very educational for me as it relates to what I have
read many times on the boards ut it all on paper first", and,
"determine all information to be stored in the database and its
relations
to other tables" before creating them. If this was done, I would know
that the SSN and VIN Numbers were not suitable candidates for a
Unique
Identifier.
Point Taken !
Thanks for all your insight ,as usual

VL


"tina" wrote in message
news well, glad i could help.

but i have to point out that the VIN analogy is not apt. a VIN is not
just a
collections of numbers/letters, it is a very specific code that
identifies
manufacturer, make and model of vehicle, model year of vehicle,
sometimes
where the vehicle was built, sometimes engine size, sometimes maximum
payload for trucks, and other data - as well as the last 4 to 6
digits
that
are unique to a specific vehicle that is otherwise identical to many
other
vehicles in all those other respects. so a VIN has meaning separate
from
any
tracking database that stores it, and it must often be seen and used
directly by people - such as manufacturers, owners, insurers, DMVs.

an Autonumber primary key, on the other hand, has no meaning outside
of
the
database where the data is stored, and no meaning within the
database,
except to the software - and it absolutely should *not* be used with
the
idea of attaching a meaning to it. it also should not be seen by
anyone
who
uses the database, at any time - excepting only that the developer
may
look
at Autonumber values from time to time while building and/or
troubleshooting
the database.

hth


"accessquestions" wrote in message
...
good point.
the Auto Number is sort like a VIN on a car, you rarely have to see
it,
but
its there and its serving a purpose,, and nobody committs it to
memory,
if
they would it would just confuse them. you convinced me, im an auto
number
guy from now on.
thanks


"tina" wrote in message
...
well, personally, i would not use the SSN as a primary key, for 2
reasons.
first, SSNs are unfortunately not unique; there are many, many
duplicates
floating around out there. of course, only one person is using a
specific
SSN *legally*, but unless you want to get into tracking down
fraudulent
use... second, if you use the SSN as primary key, you'll have it
populating
multiple tables, potentially scattered all over your database.
again,
personally, i would safeguard an SSN the same way i would a credit
card
number, in my database; put it in one place, and one place only,
so
it's
easier to protect by whatever means you use to secure proprietary
data.

having said all that, there's no reason you can't use an
Autonumber
as
the
primary key field for your employee table *as long as it is a
value
the
has
no meaning to anyone, only to Access*. your user should never see
an
Autonumber primary key, or use it directly to search for a record
in
the
table. don't confuse how your *user* finds a certain record in a
table
with
how *Access* identifies each record in a table. you can easily use
an
Autonumber primary key and still allow your user to seach for an
employee
record using the person's SSN.

hth


"accessquestions" wrote in message
...
Hello,
As far as a Primary Key is concerned (for an Employee Table)
I have read numerous times on the boards NOT to use the Auto
Number
feature,
I'm not sure exactly why, although I could see where the random
number
can
confuse some.
Would anyone recommend using a SSN instead? It will be unique,
and
it
will
serve as a good reference to the employee.
Any thoughts of why or why not?

Thanks Again !

VL

you're welcome


"accessquestions" wrote in message
...
alrighty then,
thanks Tina!



"tina" wrote in message
...
comments inline.

"accessquestions" wrote in message
...
Good point. But does this mean that setting the No Duplicates option
on
a
particular field only applies if that field is a Primary Key?

no.

I was under
the impression that it you can select No Dups for any Field.

that is correct.

So, with the
SSN or VIN anaolgies, couldnt one still ensure the uniqueness of them,
even
if they werent the PriKey?

well, to be more precise, you can set a unique index on any field to
ensure
that no duplicate values can be entered in the field. you may have
duplicate
*data*, but you can't enter that duplicate data in a field with a Unique
index setting.

And still use the AutoNumber option for the
Primary?

correct.




"BruceM" wrote in message
...
I would like to point out that a primary key does not need to be
meaningless to the user. A sequential number such as 000001, 000002,
etc.
may be used for invoice numbers or the like. I don't see any reason
why
that number can't be the PK.
There are legal reasons why SSN should not be widely available in a
database, as it would be if it was part of relationships. However, I
would think an employer would want to know if the number is not
unique.
If John Smith leaves the company, and five years later Jill Jones
shows
up
with John Smith's SSN, I expect it would be a real mess for a company
that
simply ignores the duplication.
A VIN as a PK could get a bit murky for an insurance company or the
DMV
if
a vehicle changes hands, but I expect either entity would want to
know
if
a VIN is being reused. A VIN could make sense as a PK in some
situations.
Uniqueness is one thing; suitablility for use as a PK is another.
There
are a lot of possible circumstances, each of which needs to be
considered
separately.

"accessquestions" wrote in message
...
understood. I actually thought of that after I posted.
Just goes to show that it really takes an analytical mind to perfect
the
concepts of relational databases.
A VIN, like you state, serves a purpose, and is an attribute (an
important one) of a vehicle. Not just a random number to identify
it's
uniqueness in a database. If it were a Database containing vehicle
information, the VIN would not be a proper number to use a primary
key
identifier either (just like a SSN), but instead a value relating to
another value. And even then, a random number, such as Aceess'
AutoNumber should be used for the system to identify it and its
uniqueness.
This dialogue was very educational for me as it relates to what I
have
read many times on the boards ut it all on paper first", and,
"determine all information to be stored in the database and its
relations
to other tables" before creating them. If this was done, I would
know
that the SSN and VIN Numbers were not suitable candidates for a
Unique
Identifier.
Point Taken !
Thanks for all your insight ,as usual

VL


"tina" wrote in message
news well, glad i could help.

but i have to point out that the VIN analogy is not apt. a VIN is
not
just a
collections of numbers/letters, it is a very specific code that
identifies
manufacturer, make and model of vehicle, model year of vehicle,
sometimes
where the vehicle was built, sometimes engine size, sometimes
maximum
payload for trucks, and other data - as well as the last 4 to 6
digits
that
are unique to a specific vehicle that is otherwise identical to
many
other
vehicles in all those other respects. so a VIN has meaning separate
from
any
tracking database that stores it, and it must often be seen and
used
directly by people - such as manufacturers, owners, insurers, DMVs.

an Autonumber primary key, on the other hand, has no meaning
outside
of
the
database where the data is stored, and no meaning within the
database,
except to the software - and it absolutely should *not* be used
with
the
idea of attaching a meaning to it. it also should not be seen by
anyone
who
uses the database, at any time - excepting only that the developer
may
look
at Autonumber values from time to time while building and/or
troubleshooting
the database.

hth


"accessquestions" wrote in message
...
good point.
the Auto Number is sort like a VIN on a car, you rarely have to
see
it,
but
its there and its serving a purpose,, and nobody committs it to
memory,
if
they would it would just confuse them. you convinced me, im an
auto
number
guy from now on.
thanks


"tina" wrote in message
...
well, personally, i would not use the SSN as a primary key, for
2
reasons.
first, SSNs are unfortunately not unique; there are many, many
duplicates
floating around out there. of course, only one person is using a
specific
SSN *legally*, but unless you want to get into tracking down
fraudulent
use... second, if you use the SSN as primary key, you'll have
it
populating
multiple tables, potentially scattered all over your database.
again,
personally, i would safeguard an SSN the same way i would a
credit
card
number, in my database; put it in one place, and one place only,
so
it's
easier to protect by whatever means you use to secure
proprietary
data.

having said all that, there's no reason you can't use an
Autonumber
as
the
primary key field for your employee table *as long as it is a
value
the
has
no meaning to anyone, only to Access*. your user should never
see
an
Autonumber primary key, or use it directly to search for a
record
in
the
table. don't confuse how your *user* finds a certain record in a
table
with
how *Access* identifies each record in a table. you can easily
use
an
Autonumber primary key and still allow your user to seach for an
employee
record using the person's SSN.

hth


"accessquestions" wrote in message
...
Hello,
As far as a Primary Key is concerned (for an Employee Table)
I have read numerous times on the boards NOT to use the Auto
Number
feature,
I'm not sure exactly why, although I could see where the
random
number
can
confuse some.
Would anyone recommend using a SSN instead? It will be unique,
and
it
will
serve as a good reference to the employee.
Any thoughts of why or why not?

Thanks Again !

VL