Asger Blond
ab-mi at post3.tele.dk
Sat Jun 27 07:02:19 CDT 2009
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
-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Drew Wutka
Sendt: 26. juni 2009 18:51
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Custom autonumbering
In a relational database, a one to one relationship should be a negligible
'overhead' situation.
However, to clarify a point, if you are inserting a record into
tblOrderNumbers for each record inserted into tblOrders, why not use the
Autonumber in tblOrders? It too would be sequential. My suggestion was in
the condition where records may be entered into tblOrders (creating an
OrderID) that are not actually used, thus requiring the second table for a
sequential number.
I used this process in our old website's shopping cart. Each 'cart' was in
a single table (for cart specific data, child data, such as parts, prices,
etc, was stored in other tables, linked to the CartID). Accounting wanted a
sequential 'Invoice Number'. The CartID wouldn't do, because a cart could
be created, and not actually ordered. So I had a separate Invoice Number
table (like tblOrderNumbers) with an AutoNumber and the CartID. That record
was only created at the point that the shopping cart was successfully
'ordered'.
So in your situation, if you have a form, where they are putting in
information, you simply provide a button for 'Complete Order'...on that
button's OnClick event, you would create the record in tblOrderNumbers. And
from any other sources, use a query with that join in place, to display the
OrderNumber. Keep ALL child data linked to the OrderID (making the
OrderNumber a data field, not a key).
Drew
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: Friday, June 26, 2009 5:36 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Custom autonumbering
Thanks Drew. Just to make sure I get you right, is this what you are
suggesting:
tblOrders: tblOrderNumbers:
OrderID (PK, Autonumber) OrderNumber (PK, Autonumber)
Date OrderID (FK, Long integer)
CustomerID
etc.
Then tblOrders and tblOrderNumbers would have a one-to-one relationship on
OrderID, and I could fetch the sequential OrderNumber querying
tblOrderNumbers. This may be viable, but it would require an insert to
tblOrderNumbers for each new insert to tblOrders, and it would require
joining two tables for querying orderinformation including the ordernumber.
I know using DMAX imposes an overhead, but is this more expensive in terms
of performance than using the two-table solution?
Asger
-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Drew Wutka
Sendt: 26. juni 2009 07:20
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Custom autonumbering
Hi Asger, what's the reasoning for just not using an Autonumber field?
It looks like you do want it sequential. You can start it at any number
you want, by appending the number (-1) into the table, then compacting
the database, and deleting that appended record.
Now, if it must be sequential, and you need to provide the ability to
allow for slips, which would negate a normal Autonumber field, what I
would recommend is to use two autonumber fields. One in the primary
table, with the actual data. Make this the actual key too. Then, when
an 'order number' is really ready to be created (so when you would run
your normal code), have a second table with another Autonumber (which
would be the order number), and a plain long integer as the foreign key
from the data table.
That will let you keep the second table sequential, while not having to
worry about duplicates.....
Drew
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: Thursday, June 25, 2009 7:31 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Custom autonumbering
Using Autonumber for surrogate primary keys, I often need to manage
custom
autonumbering for alternate unique keys, e.g. for order numbers.
I know this subject has been discussed several times before, but I
couldn't
find exactly what I was looking for in the archive.
How do you manage custom autonumbering?
I do it this way, which may not be the most efficient, so I'm calling
for
other suggestions:
1. On the form I set the property DefaultValue at design-time for the
control holding the custom autonumbering field to:
=DMAX(<field>,<table>) + 1
This will do in single-user environments, but will invite duplicate
values
in multi-user environments.
So I use this run-time code in addition to the design-time DefaultValue:
2. Assuming the custom autonumbering field is called OrderNumber in a
table
called tblOrders:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim intOrderNumber_Default As Integer
Dim intOrderNumber_RunTime As Integer
If NewRecord Then
intOrderNumber_Default = OrderNumber.Value
intOrderNumber_Runtine = DMAX("OrderNumber","tblOrders") + 1
If intOrderNumber_Runtime <> intOrderNumber_Default Then
OrderNumber.Value = intOrderNumber_Runtime
MsgBox "Another user has created a new order with the number "
& _
intOrderNumber_Default & vbNewline & _
"Your order has got the number " &
intOrderNumber_Runtime
End If
End If
End Sub
Do you think this solution has any pitfalls, or do you just have another
more efficient/intelligent custom autonumbering?
Asger
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
The information contained in this transmission is intended only for the
person or entity
to which it is addressed and may contain II-VI Proprietary and/or II-VI
Business
Sensitive material. If you are not the intended recipient, please contact
the sender
immediately and destroy the material in its entirety, whether electronic or
hard copy.
You are notified that any review, retransmission, copying, disclosure,
dissemination,
or other use of, or taking of any action in reliance upon this information
by persons
or entities other than the intended recipient is prohibited.
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
The information contained in this transmission is intended only for the
person or entity
to which it is addressed and may contain II-VI Proprietary and/or II-VI
Business
Sensitive material. If you are not the intended recipient, please contact
the sender
immediately and destroy the material in its entirety, whether electronic or
hard copy.
You are notified that any review, retransmission, copying, disclosure,
dissemination,
or other use of, or taking of any action in reliance upon this information
by persons
or entities other than the intended recipient is prohibited.
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com