Primary Key Autonumbers

[Guest]

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

 

[BruceM]

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/download3.asp?SampleName=AutonumberProblem.mdb
Note particularly the method for a multi-user database. It is possible to
implement a similiar system using VBA.

 

[Rick Brandt]

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.

 

[Lynn Trapp]

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/conradsystems/accessjunkie.html

 

[Jeff Boyce]

David

I agree with the other responders, even though I am one of the "unfit for
human consumption" folks re: 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

 

[BruceM]

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]

Bruce

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

Jeff

 

[Rick Brandt]

David

I agree with the other responders, even though I am one of the "unfit
for human consumption" folks re: 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.

 

[Lynn Trapp]

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/conradsystems/accessjunkie.html

 

[Fred Boer]

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

 

[Jeff Boyce]

Thanks for adding to the consensus of community.

But the concept of thousands of Access database users who all can (could)
see Autonumbers leaves me faint... <g>

Jeff

 

[Guest]

Make that three.....

I am in the "not for human comsumption" camp on this point. However, I do
make an exception to that stance in one case.

I maintain two databases for a mass-mailing program. When we send out our
mail campaigns, we identify the prospects by the Primary Key in the prospect
table. No way 25,000 people are going to get together and compare the "ID"
numbers on their letters to see if there are gaps in the sequence. With a
response rate of 2% or less, it should be obvious to everyone involved that
there are gaps on the in-bound side. However, we DO make a concession to the
real world by padding all of the IDs out to 6 digits by pre-pending leading
0's as needed. We ask for the "6-digit" ID on the letter in handling
responses.

I suspect that there are probably other, very specific situations where
hiding the primary key from a user is not necessary, but they should always
be considered carefully.

 

[Lynn Trapp]

But the concept of thousands of Access database users who all can (could)

see Autonumbers leaves me faint... <g>

You're just always fainting, Jeff <g,d,&r>

--
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/conradsystems/accessjunkie.html

 

[Lynn Trapp]

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!)

Fred,
About 4 years ago, I was moved over to one of our Oracle development teams.
I spend my time supporting Oracle Purchasing and Warehousing now. I still do
some Access development after hours, but it's pretty minimal.

--
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/conradsystems/accessjunkie.html

 

[Guest]

Thanks for all the input. What would be your thoughts on using a random
autonumber for Purchase Orders? The number has no meaning other than to
identify each purchase order. This number is quoted as our reference on
incoming invoices.

Despite all the reasons for not displaying/using the autonumber, I still
fail to see what is inherently wrong in it (when used in the right
circumstances). I suppose it is more a question of the business model, rather
than database design itself?

Dave

 

[Lynn Trapp]

The problem with using a random AutoNumber is that when you use random it
will often return negative numbers. Of course, you could display it as
ABS(YourAutoNumber), but that could cause confusion if you ever ended up
with 2 POs with numbers that were the negative of each other. Using the
sequential autonumbering seems the best for me, if you don't want to use the
approach that Bruce suggested:

http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

--
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/conradsystems/accessjunkie.html

 

[BruceM]

I could certainly be wrong, but I think that David's reference to "random
autonumber" meant the standard autonumber, which can leave gaps in the
numbering, but which is not random.
David, Access does not care what you use for invoice numbers. If you have
users who can remember everything you tell them, and who will not be thrown
off course by an apparent missing invoice, and if your auditors and
accountants and so forth are comfortable with "missing" invoice numbers,
then you can use it. There is nothing "inherently" wrong with it as long as
your business environment can accept the result. Still, I think it would be
worthwhile to check out the link I provided early in this thread, and thay
Lynn provided in the message to which I am replying. It's really not that
hard to have a systematic numbering system, just in case somebody wants one
some day.

 

[Fred Boer]

Thanks, Lynn... We here at the Bureau Of Idle Curiosity Bordering On
Rudeness like to keep our records up to date!

Fred
Research Officer 1st Class, BOICBOR

 

[Lynn Trapp]

Well, I think that curiosity is only dangerous to cats...<g>

--
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/conradsystems/accessjunkie.html

 

[Guest]

I tried the Autonumber module provided in the link. Using the example form,
the form had to be closed and opened again for the "gap" in the autonumber to
disappear. For example:

Create a new record (ID4). Start to create another (ID5). Go back to ID4 and
delete it. Now, when you start to create another record, you would expect it
to be ID4, but it is still ID5. Close the form, open it again, and the new
record is created as ID4.

It would appear that, in any DB that allows deletes, the Access autonumber
does the job just as well as any other. In a DB that does not allow deletes,
Rogers autonumber is better. The question is, if deletes are allowed, and you
delete the very last record, should the next record created take its number
(a la Rogers autonumber when you close and reopen the form) or should it take
the next number on (a la Access autonumber)?

In the end, it is much more about what the number will be used for, rather
than the blanket approach of "don't show autonumbers to the users".

Thanks for all the input.

Dave