DWUTKA at marlow.com
DWUTKA at marlow.com
Thu May 6 11:35:30 CDT 2004
Don't get me wrong. I don't use Autonumbers 'willy-nilly' as 'human' identifiers. 99% of the time, I do use some other field, or 'calculated' value. There are those one percents though, where I feel very comfortable letting a user see an Autonumber. I used the Ticket number as an example, because it was an in house project, that I had/have complete control over. It's not something I have to push out the door, and let someone else play around with it. I don't think I can recall any instance of external usage of AutoNumbers as 'visible' keys. There may have been some, but I can't say either way. I do tend to stick to something the user can identify with. (ie, employee numbers are usually 'built' from other stuff', Invoices always seem to have date/month info in them, etc.). Well, I think we beat this horse to death already. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte Foust Sent: Thursday, May 06, 2004 11:21 AM To: Access Developers discussion and problem solving Subject: RE: [AccessD] AutoNumber To Start From 900120 I *always* program around that particular possibility. I've been bitten every time I didn't, and I'm running out of room for scars! <G> Account numbers, Employee numbers, invoice numbers, and any other "numeric" identifiers tend to change their structure and logic without notice. When they casually mention that they've decided to go from 5 digits to 9, I just say, "no problem" and run an update query. Charlotte Foust -----Original Message----- From: DWUTKA at marlow.com [mailto:DWUTKA at marlow.com] Sent: Thursday, May 06, 2004 8:11 AM To: accessd at databaseadvisors.com Subject: RE: [AccessD] AutoNumber To Start From 900120 I see your point. However, at this point in time, I would rather stick with a simple Autonumber, which saves current development time, then rolling my own. I say this because I can imagine far worse 'requests' from management then to change an autonumber. Not too mention I get paid by the hour, so I honestly don't really care if I have to rebuild something. I try to build a lot of flexibility into my apps, but I can't program around every possibility. Not arguing, just expressing my viewpoint on it. Also, the IS Request system isn't 'seen' by upper management. It's an 'internal' IS app. The only interface anyone else sees is when they put in a request. 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 10:59 AM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] AutoNumber To Start From 900120 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 > > -- _______________________________________________ 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 -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com