autonumber queestion

Discussion in 'Access General' started by Ted, Sep 23, 2013.

  1. Ted

    Ted Guest

    I have a database for personal use in my woodturning hobby. I want to create
    an item number for each item I make. One table has a field ItemNumber (two
    letters, ie, PS, PM, PB, WS, etc) Another table will have the two letter
    field for WoodType (two letters, ie, BO, CO, MB, etc).

    I want to create an autonumber field that will combine ItemNumber and
    WoodType and then add the next sequential number for the 4 letter prefix.

    For example, PSCO001, PSCO002, PMCO001, PBMF001
     
    Ted, Sep 23, 2013
    #1
    1. Advertisements

  2. Don't.

    This is called an "Intelligent Key" - and unfortunately that's not a
    compliment.

    A basic principle of relational databases is that fields should be "atomic" -
    having one and only one piece of information.

    The wood and the finish are each independent attributes of the item and should
    not be part of its key; just have fields for the wood type and the finish, and
    use a (meaningless) sequential autonumber as the primary key. You can
    concatenate the three fields for display if you need it.

    One thing to consider - a vendor at our local farmers' market makes beautiful
    turned wood pieces of several types of wood neatly glued together. That might
    not be your style today but you wouldn't want to rule it out just because of a
    software design flaw, would you?
    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Sep 23, 2013
    #2
    1. Advertisements

  3. Ted

    Ted Guest

    I can combine the first to parts, it is the autonumber that I am having the
    issue with.

    The wood type and item are important as if there are two of the same wood
    type and item type, then one should have 001 as the suffix and the second
    should have 002.

    The si

    "John W. Vinson" wrote in message

    Don't.

    This is called an "Intelligent Key" - and unfortunately that's not a
    compliment.

    A basic principle of relational databases is that fields should be
    "atomic" -
    having one and only one piece of information.

    The wood and the finish are each independent attributes of the item and
    should
    not be part of its key; just have fields for the wood type and the finish,
    and
    use a (meaningless) sequential autonumber as the primary key. You can
    concatenate the three fields for display if you need it.

    One thing to consider - a vendor at our local farmers' market makes
    beautiful
    turned wood pieces of several types of wood neatly glued together. That
    might
    not be your style today but you wouldn't want to rule it out just because of
    a
    software design flaw, would you?
    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    Ted, Sep 29, 2013
    #3
  4. If you want an identifying code as you suggest, you'll have to create it
    yourself.

    "Autonumbers" are specifically intended to provide a unique identification
    (sometimes known as a "surrogate key"). Although in their most common form,
    they are monotonically increasing numbers, with some exceptions -- you
    can't count on not having gaps in the sequence. Autonumbers work in a
    specified way, and there's no provision for "custom autonumbers".

    Perhaps you did not understand from John that, if you violate relational
    rules (by creating an 'intelligent key'), sooner or later, your
    not-quite-relational database will be more difficult for you to use.

    I'm sure you feel that you *need* that key, but believe me, you can query
    your database to retrieve all (or a subset of) items with a specific wood, a
    specific finish, and, separately, a count of how many of those). But, I'd
    wager that some here can show you how to accomplish the same purpose and
    still follow proper relational database design.
     
    Access Developer, Sep 29, 2013
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.