[AccessD] Custom autonumbering

Hi Asager,

I doubt if you will get more unique within the normal Access program then to
use:

Now() followed by a now() from (a reset of date - arbitrary number) added to
the original Now() and then add on the autonumber

What I am saying is, it is so easy to come up with a number that is "almost"
guaranteed to be unique..

Pick a Star in the galaxy. Any star..how many miles is that from
venus...that will do.

I am NOT being facetious.  If you devise a system of generating a unique
number  and you make your "system" unique enough, it will work..

Max



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: 01 July 2009 23:58
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Custom autonumbering

Actually I don't want to mimic the AutoNumber field type. I want a custom
autonumber which is 1) reliable sequential, and 2) customable. Your
two-table solution provides the first requirement and I will consider it for
future use. But sometimes I need to supply an easy way of creating a new
offset for the autonumber: e.g. using the current year as a leading number
the user should have the ability to change the number let's say 20098001 to
20100001, making this a new starting point for the sequential numbering
(normally this custom autonumber field should be locked on the form and only
editable providing a password).
I still find the DMAX-solution easier to manage for the second requirement.
But as said I appreciate your proposal for a reliable sequential numbering.

Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Drew Wutka
Sendt: 1. juli 2009 23:52
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Custom autonumbering

I've used Autonumbers as actual values (like Ticket numbers in a request
system) where the number doesn't have to truly be sequential.  But in
the case you are talking about, it seems kind of odd to create a routine
that mimics the AutoNumber field type, instead of just using that field
type (in a separate table to keep it sequential).  Hope it works out.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: Saturday, June 27, 2009 7:02 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Custom autonumbering

Thanks Drew - your explanation corresponds my reading your first
posting.
I always use AutoNumber exclusively for surrogate primary keys, never
for
alternate natural keys like OrderNumber.
In my opinion AutoNumber (and the equivalent in SQL Server: Identity) is
designed for surrogate keys, not for natural keys.
You could use AutoNumber for natural keys, but then you are inviting
troubles. In Access pressing Esc on entering a new record will increment
the
AutoNumber, leaving gaps in the numbering. And depending on which
version of
Access you use, a Compact Database will reset the autonumber to the next
value after your records INcluding deleted trailing records, or it will
reset to the next record after your records EXcluding deleted trailing
records. Bottom line: You simply can't predict the value of an
AutoNumber
field.
This is what makes AutoNumber an excellent candidate for surrogate
primary
keys because this kind of keys is supposed to be meaningless.
You can ignore the special behaviour of AutoNumber if you use it as a
surrogate key like OrderID, but you certainly can't ignore it if you
want a
natural key value like OrderNumber, which is supposed to be meaningful.

Asger


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com