Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    1

    Unanswered: Help for Newbie: How to create an autonumber

    Hi guys/gals. An intro, I've just pick up some database basic skills and currently doing a simple project.

    I really need advise on how do I create a field that can generate autonumbering for eg. scenario

    There are 3 types of service_id like CTXXXX, GPXXXX, and STXXXX (where XXXX are running numbers). What I need is to have the XXXX auto generate and running. Also if I were to delete an old record for example CT0033, the latest number eg. CT1111 will not be changed to cover up for the missing CT0033.

    I apologize if the description is a bit improper, but I'm new to this. Really appreciate anyone that can help. Thanks!!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest the IDENTITY property when you create the table. You'll have to "decorate" the resulting number with the letters you need, but that's trivial.

    -PatP

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    my money's on access

    and what do you mean by basic skills?

    can you spell dba?

    Is this homework?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sarcasm, Brett? How unlike you!

    NOT!



    If all three of these services are stored in the same table, then you will run into difficulties with IDENTITY unless you don't mind each service being numbered consecutively. IDENTITY will autonumber for the entire table, but not for individual services within the table.

    Your best bet is to store the last ID number used for each service in a separate table, and then use a trigger on your data table that looks up the last code used, increments it by 1, and creates the new services codes.

    But this is not simple SQL.

    An alternative would be to denormalize your data and store the services in separate tables each with their own IDENTITY property, but this is frowned upon for many good reasons.

    Actually, your best course of action would be to drop the idea of creating these codes in the first place, since such pseudo-surrogate keys violate several principles of database application design. That is why they are difficult to code.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •