Primary Key Autonumbers

I've been reading lots of posts regarding not showing Primary Key autonumbers
to users. Could I ask, why is this a bad idea?

I've designed a DB for handling purchase orders. The primary key of the main
order table is an autonumber field. I use this autonumber field to generate
my Purchase Order Number in a report:
"JobNumber/AutoNumber/RequestingInitials/AuthorisingInitials".

Obviously, the autonumber increments sequentially, but can end up keaving
gaps between the numbers. Again I ask, Why is it a bad idea showing
autonumbers to users? How would you recommend generating a unique Order
Number on the fly?

Thanks

Dave

If any old number will do for the invoice number, I suppose you could use
autonumber as long as users, accountants, managers, auditors, and so on
understand that there is nothing irregular about having a sequence of five
invoices in which the number increments by one, followed by a "missing"
invoice number, followed by two more in sequence, followed by a gap of four
in the numbers ....
Here is one way of creating an incrementing number:
http://www.rogersaccesslibrary.com/d...berProblem.mdb
Note particularly the method for a multi-user database. It is possible to
implement a similiar system using VBA.

"David M C" wrote in message
...
I've been reading lots of posts regarding not showing Primary Key
autonumbers
to users. Could I ask, why is this a bad idea?

I've designed a DB for handling purchase orders. The primary key of the
main
order table is an autonumber field. I use this autonumber field to
generate
my Purchase Order Number in a report:
"JobNumber/AutoNumber/RequestingInitials/AuthorisingInitials".

Obviously, the autonumber increments sequentially, but can end up keaving
gaps between the numbers. Again I ask, Why is it a bad idea showing
autonumbers to users? How would you recommend generating a unique Order
Number on the fly?

Thanks

Dave

"David M C" wrote in message
...
I've been reading lots of posts regarding not showing Primary Key autonumbers
to users. Could I ask, why is this a bad idea? [snip]

The advice "Users should never see an AutoNumber field" is over-stated IMO. It
would only be a problem if the users have certain expectations about how the
number will behave. As long as those expectations do not exist or are explained
away then using the AN in a visible manner is completely fine.

I generally only do this in databases where deletions are allowed so nobody
using the app is the least bit concerned about "gaps" because they know that
records get deleted.

I have also used the random AN feature and then displayed the value in
Hexidecimal. This provides a consistent 8 character length, no negative sign,
and it is *really* obvious to all users that it is randomly generated
eliminating any concern about what the next (or last) value is.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Let me add my agreement to what Bruce and Rick have said. The problem with
allowing users to see the values of an autonumber field really only exist if
the users expect the value to have some substantial meaning and/or to be
sequential. Where I work we trained the users long ago to have different
expectations.

The most important thing for you to understand is the purpose of a Primary
Key. It is intended to uniquely identify a record within the database. Using
an AutoNumber may or may not uniquely identify the values in a given record.
Thus, it is important to create a unique index on a Candidate Key that will
accomplish that. Let me illustrate the point. If you have an Employees table
with an AutoNumber field as the Primary Key, but with no unique index on
other fields, it is possible to end up with data like the following.

EmployeeID LastName FirstName Address
1 Smith Jack
111 1st St.
2 Smith Jack
111 1st St.

Now, for the purpose of the database engine, those are unique records.
However, they may or may NOT be unique in the real world. It is very
possible to have 2 Jack Smiths living at the same address and working for
the same company. An AutoNumber will NOT guarantee that the values in the
rest of the fields is unique. Also, with tables like Employee tables and
Person tables, it can be very difficult to find another Candidate Key that
will uniquely identify the values. You may even have to allow users to enter
identical information if they believe it is validly a different person.

For other kinds of tables -- such as your Purchase Order table -- there is
no reason that an AutoNumber field cannot be used for the PO Number AND also
made available to the users. Purchase Order Numbers typically have no
meaning anyway and it doesn't matter whether or not they are sequential. The
same would probably be true of an Invoices table. However, for an AP
application from which checks are issued and deposits are entered, it is not
a good idea to use an AutoNumber field because of the problem with gaps. A
gap in a check number or deposit number sequences throw accountants into the
heebie jeebies, for good reason. For tables like those you will want to use
some other process for developing the unique and sequential numbers.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conrad...essjunkie.html



"David M C" wrote in message
...
I've been reading lots of posts regarding not showing Primary Key
autonumbers
to users. Could I ask, why is this a bad idea?

I've designed a DB for handling purchase orders. The primary key of the
main
order table is an autonumber field. I use this autonumber field to
generate
my Purchase Order Number in a report:
"JobNumber/AutoNumber/RequestingInitials/AuthorisingInitials".

Obviously, the autonumber increments sequentially, but can end up keaving
gaps between the numbers. Again I ask, Why is it a bad idea showing
autonumbers to users? How would you recommend generating a unique Order
Number on the fly?

Thanks

Dave

David

I agree with the other responders, even though I am one of the "unfit for
human consumption" folks Autonumbers. They can be used if everyone
understands their constraints.

I disagree with the other responders' apparent assumption that it is enough
to explain that ANs are not guaranteed to be sequential. People forget, new
people come on board, and the explanation and "sign here in blood that you
understand" may never be sufficient to prevent the
misunderstanding/misconception.

On the other hand, a custom sequence number system, in my opinion, is more
likely to "behave" the way a user expects. I'm for reducing the number of
potential misconceptions/confusions...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"David M C" wrote in message
...
I've been reading lots of posts regarding not showing Primary Key
autonumbers
to users. Could I ask, why is this a bad idea?

I've designed a DB for handling purchase orders. The primary key of the
main
order table is an autonumber field. I use this autonumber field to
generate
my Purchase Order Number in a report:
"JobNumber/AutoNumber/RequestingInitials/AuthorisingInitials".

Obviously, the autonumber increments sequentially, but can end up keaving
gaps between the numbers. Again I ask, Why is it a bad idea showing
autonumbers to users? How would you recommend generating a unique Order
Number on the fly?

Thanks

Dave

I know from experience that explaining is rarely enough. Users are stunned
over and over by situations that haven't changed in years. They respond "I
didn't know that" to things that have been explained in documented training
sessions. By listing a wide assortment of people who would need to
understand a non-sequential numbering system I intended to discourage the
use of a visible autonumber.

"Jeff Boyce" wrote in message
...
David

I agree with the other responders, even though I am one of the "unfit for
human consumption" folks Autonumbers. They can be used if everyone
understands their constraints.

I disagree with the other responders' apparent assumption that it is
enough to explain that ANs are not guaranteed to be sequential. People
forget, new people come on board, and the explanation and "sign here in
blood that you understand" may never be sufficient to prevent the
misunderstanding/misconception.

On the other hand, a custom sequence number system, in my opinion, is more
likely to "behave" the way a user expects. I'm for reducing the number of
potential misconceptions/confusions...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"David M C" wrote in message
...
I've been reading lots of posts regarding not showing Primary Key
autonumbers
to users. Could I ask, why is this a bad idea?

I've designed a DB for handling purchase orders. The primary key of the
main
order table is an autonumber field. I use this autonumber field to
generate
my Purchase Order Number in a report:
"JobNumber/AutoNumber/RequestingInitials/AuthorisingInitials".

Obviously, the autonumber increments sequentially, but can end up keaving
gaps between the numbers. Again I ask, Why is it a bad idea showing
autonumbers to users? How would you recommend generating a unique Order
Number on the fly?

Thanks

Dave

Bruce

So there are at least two of us who prefer not to make ANs visible to
end-users... g

Jeff

"BruceM" wrote in message
...
I know from experience that explaining is rarely enough. Users are stunned
over and over by situations that haven't changed in years. They respond "I
didn't know that" to things that have been explained in documented training
sessions. By listing a wide assortment of people who would need to
understand a non-sequential numbering system I intended to discourage the
use of a visible autonumber.

"Jeff Boyce" wrote in message
...
David

I agree with the other responders, even though I am one of the "unfit for
human consumption" folks Autonumbers. They can be used if everyone
understands their constraints.

I disagree with the other responders' apparent assumption that it is
enough to explain that ANs are not guaranteed to be sequential. People
forget, new people come on board, and the explanation and "sign here in
blood that you understand" may never be sufficient to prevent the
misunderstanding/misconception.

On the other hand, a custom sequence number system, in my opinion, is
more likely to "behave" the way a user expects. I'm for reducing the
number of potential misconceptions/confusions...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"David M C" wrote in message
...
I've been reading lots of posts regarding not showing Primary Key
autonumbers
to users. Could I ask, why is this a bad idea?

I've designed a DB for handling purchase orders. The primary key of the
main
order table is an autonumber field. I use this autonumber field to
generate
my Purchase Order Number in a report:
"JobNumber/AutoNumber/RequestingInitials/AuthorisingInitials".

Obviously, the autonumber increments sequentially, but can end up
keaving
gaps between the numbers. Again I ask, Why is it a bad idea showing
autonumbers to users? How would you recommend generating a unique Order
Number on the fly?

Thanks

Dave

Jeff Boyce wrote:
David

I agree with the other responders, even though I am one of the "unfit
for human consumption" folks Autonumbers. They can be used if
everyone understands their constraints.

I disagree with the other responders' apparent assumption that it is
enough to explain that ANs are not guaranteed to be sequential. People
forget, new people come on board, and the explanation and
"sign here in blood that you understand" may never be sufficient to
prevent the misunderstanding/misconception.

On the other hand, a custom sequence number system, in my opinion, is
more likely to "behave" the way a user expects. I'm for reducing the
number of potential misconceptions/confusions...

Another thing to consider though is the usage and type of data in the
database. Some databases never or rarely display records to users as a
list. It is only when displayed as a list that a gap will even be
discovered by the user much less cause them to be concerned about it.

For example I had a call center app a few years ago and we gave each call a
number. The ONLY purpose of the number was so we could tell the person on
the phone "if you contact us further about this incident please refer to
this call number".

With several people recording calls at the same time there was no
expectation that if the last call I took was 123456 that the next one would
be 123457 because the user knew that there were other users also taking
calls and consuming numbers.

Reporting and searching was always by Caller Name, Call Taker Name, Calls
taken in a certain time period, etc.. There would have been zero utility in
displaying the calls in chronological order which would be the only way that
anyone might notice a gap in the number sequence.

I never "trained" anyone about how the number sequence should or would
behave and I never received a single question about it either. If someone
had asked I would have said "It's a unique number given to each call.
That's all you need to know about it."

I completely agree that in financial-type areas a number sequence with gaps
can be problematic or even a legal issue, but not every app falls into this
category.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

I disagree with the other responders' apparent assumption that it is
enough to explain that ANs are not guaranteed to be sequential. People
forget, new people come on board, and the explanation and "sign here in
blood that you understand" may never be sufficient to prevent the
misunderstanding/misconception.

Jeff,
Actually, in my case, it's not an assumption but an observation from
experience. When I started working here I found, literally, thousands of
Access databases that were peppered all over with visible AutoNumbers. I
don't ever recall anyone complaining about there being gaps in the sequence.
That might have changed since I left the Access team, but it was my
experience when I was doing Access development.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conrad...essjunkie.html

Dear Lynn:

What d'ya mean "left the Access team"?! Who's going to play right wing now!?

And what's with the past tense when discussing Access development? Inquiring
minds want to know! (Well, one maybe!)

Cheers!
Fred


"Lynn Trapp" wrote in message
...
I disagree with the other responders' apparent assumption that it is
enough to explain that ANs are not guaranteed to be sequential. People
forget, new people come on board, and the explanation and "sign here in
blood that you understand" may never be sufficient to prevent the
misunderstanding/misconception.

Jeff,
Actually, in my case, it's not an assumption but an observation from
experience. When I started working here I found, literally, thousands of
Access databases that were peppered all over with visible AutoNumbers. I
don't ever recall anyone complaining about there being gaps in the
sequence.
That might have changed since I left the Access team, but it was my
experience when I was doing Access development.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conrad...essjunkie.html