I'd like to hear what the non-exceptions say about this.
John,
The reason to use two fields is that it's then easier to write the sql without "left" and "right" functions. Lazy?
Adam
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Adam-
>
> Well, I'm an exception to the "rule" about using AutoNumber. If you have
> a "natural" Primary Key, then use it! But why have two fields? You can
> have one Text field that does the trick. It's not hard to strip off the
> number part to add 1. I would also force leading zeroes in the number
> part so that they sort correctly. E.G. W000019
>
> Use Format(lngNewNumber, "000000") to get the leading zeroes you need.
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: AdamF <runuphillracing@...>
> Reply-To: <MS_Access_Professionals@yahoogroups.com>
> Date: Tuesday, November 27, 2012 8:08 PM
> To: <MS_Access_Professionals@yahoogroups.com>
> Subject: [MS_AccessPros] Autonumber or not?
>
> I know you (the experts) usually prefer using an autonumber to a custom
> ID. However ....
>
> This is for a jewelry company. They identify each inventory item by a one
> letter indicating type (e.g., W=watch, R=ring), and a number. The numbers
> increment for each type of item (e.g., W1, W2, W3, ...; R1, R2, R3, ...).
> Thus, the primary keys are a combination of two fields:
> ItemType - 1 char text
> InvNo - long integer. I will be* adding these using dMax + 1.
>
> *I say "will be" because the database I took over had the different types
> of items in separate tables (with an autonumber). I initially brought them
> together via union queries (didn't have the time budgeted to do a full
> redesign until now).
>
> So, do I have a good reason to drop the autonumber?
>
> Adam
> Denver, CO
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (3) |
Tidak ada komentar:
Posting Komentar