Heenan, Lambert
Lambert.Heenan at AIG.com
Thu May 6 10:58:38 CDT 2004
Regarding the ticket number. Sure, as described everything will work just fine. But the possibility exists that one day "senior management" will rear its head and decide that they want to structure the ticket number such that they can pack some more information into it (like AM/PM indicators, or the hour of the day, or what the current temperature is, or the size of shoes worn by the user, any dumb stuff they might want to know by glancing a the "ticket number"). After they have listened to your arguments for not doing this, and instructed you that you HAVE to do it you'll need to add a new field with all the bells and whistles asked (demanded) and write the code to construct the "number". Then you'll need to rewrite any code and forms and reports that display the "ticket number" so that they display the new fanged number. On the other hand if, right from the start, you had an AutoNumber field used for linking tables, and a Ticket Number field for display then there would be no problems whatever silly numbering scheme someone comes up with. The AutoNumber field remains unchanged so relationships are kept intact. All that's needed is to rewrite the code that generates the "ticket number" . At first it would simply copy the value of the AutoNumber field into the ticket number. Later the routine could be modified any way that "they" like and still all the relationships will work. Your second case is just a specialized example of the first one, where AuthorizedNet is the demanding management group. If you'd used an indexed, no dups field other than an AutoNumber one to present to AuthorizedNet as a CartID you would have had total control over the range of values used and would not have had to reset your AutoNumber PK fields to a magic number starting point to satisfy AuthorizedNet. Lambert > -----Original Message----- > From: DWUTKA at marlow.com [SMTP:DWUTKA at marlow.com] > Sent: Thursday, May 06, 2004 11:30 AM > To: accessd at databaseadvisors.com > Subject: RE: [AccessD] AutoNumber To Start From 900120 > > There are exceptions. I use the AutoNumber field for our IS Request > Ticket > Numbers. Started it at 1000. (Just so we didn't have ticket 1, 2, etc.). > There is no 'real' importance to the ticket number, other then to refer to > an exact ticket. Most of the time, we are saying 'That ticket that so and > so put in.....'. But if I email Mark about a particular request, I may > say > Request #xyzm . Where could a problem develop with that? They don't have > to be entirely sequential, they have no other meaning then being the > identifier for the requests they represent, which is the same purpose they > have within the database itself. > > I have a good reason for changing a starting AutoNumber, however. Just > built our online Shopping Cart. It interacts with AuthorizedNet, for > credit > card purchases. AuthorizedNet requires a number that cannot be repeated > from one cart to the next. So, i use the CartID which represents each > shopping cart. After testing, I cleaned up the live database, which > removed > the test carts, and compacted the database. That reset the Autonumber, so > I > had to bump it back up, into a range which hadn't been used yet, otherwise > AuthorizedNet would have refused the transactions. > > Drew > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Heenan, > Lambert > Sent: Thursday, May 06, 2004 8:42 AM > To: 'Access Developers discussion and problem solving' > Subject: RE: [AccessD] AutoNumber To Start From 900120 > > > Quite a few replies to this question, but I'm surprised that nobody has > mentioned that if some specific value of an AutoNumber field is important > then the AutoNumber field is not being used properly (this is an old hobby > horse of mine). > > An AutoNumber field is supposed to uniquely identify a specific row in a > table. That's all. The user's need not ever see or know the value of such > an > AutoNumber field, it's used by the database to link related tables > together. > If the value of an AutoNumber field is important to a user then it is > being > given some other meaning, and that's only going to cause problems down the > line. In effect you are storing two pieces of information in one field, > and > that's contrary to the normalization principals we should all at least be > aware of. > > Even Microsoft seem to have woken up to this as A2K+ no longer reset > AutoNumber's on compacting (thanks for testing that John). > > Just my 2 cents. > > Lambert > > > -----Original Message----- > > From: Jim Hewson [SMTP:JHewson at karta.com] > > Sent: Thursday, May 06, 2004 9:22 AM > > To: Access Developers discussion and problem solving > > Subject: RE: [AccessD] Autonumber To Start From 900120 > > > > > > Issue? I thought that was feature. > > > > -----Original Message----- > > From: accessd-bounces at databaseadvisors.com > > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John W. Colby > > Sent: Thursday, May 06, 2004 8:23 AM > > To: Access Developers discussion and problem solving > > Subject: RE: [AccessD] Autonumber To Start From 900120 > > > > > > Uhh... yep. At least in A2K and earlier. AXP doesn't seem to do that. > > In > > fact I just tested in A2K SR1 and it isn't resetting the autonumber > > either, > > so a service pack may have fixed that. But it definitely used to be an > > issue so be careful. > > > > John W. Colby > > www.ColbyConsulting.com > > > > -----Original Message----- > > From: accessd-bounces at databaseadvisors.com > > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jim DeMarco > > Sent: Thursday, May 06, 2004 8:47 AM > > To: Access Developers discussion and problem solving > > Subject: RE: [AccessD] Autonumber To Start From 900120 > > > > > > Yes this will work. One caveat though: do not compact the db until > you've > > got the seed number you want inserted. Compacting will reset the > > autonumber. > > > > Jim DeMarco > > > > -----Original Message----- > > From: John W. Colby [mailto:jwcolby at colbyconsulting.com] > > Sent: Thursday, May 06, 2004 8:45 AM > > To: Access Developers discussion and problem solving > > Subject: RE: [AccessD] Autonumber To Start From 900120 > > > > > > One way that works with all versions of Access (AFAIK) is to append in a > > dummy record specifying a value one less than the number you want. Then > > go > > in and delete that record. The next record will pick up with the next > > value > > (assuming an incrementing autonumber) > > > > John W. Colby > > www.ColbyConsulting.com > >