Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

How do I make a multi-part autonumber?

Discussion in 'Information Technology' started by Dedren, Nov 4, 2005.

  1. Dedren

    Dedren
    Expand Collapse
    Guest

    How do I make a multi-part autonumber or similar incrementing unique value?
    Example:
    F05-001
    F05-002

    The "F" is the type of case it is, which comes from another value in the
    same record
    The "05" is the year it was opened, which comes from another value in the
    same record
    The "-001" is the value that increments per record

    We have been trying for a week now to think of something best we could come
    up with was making seperate tables for each part of the number that would do
    a sort of 'if' statement:
    IF (casetype=M) PUT M
    IF (casedateyear=2005) PUT 05
    THEN increment (ending+1=ending)

    I would appreciate any bit of advice anyone could give me cause I am stumped.
     
  2. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    You don't.

    "Smart keys" (the term's slightly derogatory) aren't recommended. You're
    trying to store 3 pieces of information: use 3 separate fields. You can
    create a query that concatenates them together for display purposes if you
    really need to, and use the query wherever you would otherwise have used the
    table, but storing multiple pieces of information in a single field is
    actually a violation of relational database rules.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Dedren" <Dedren@discussions.microsoft.com> wrote in message
    news:230E194E-DF69-4B14-B62C-ADF670362B68@microsoft.com...
    > How do I make a multi-part autonumber or similar incrementing unique

    value?
    > Example:
    > F05-001
    > F05-002
    >
    > The "F" is the type of case it is, which comes from another value in the
    > same record
    > The "05" is the year it was opened, which comes from another value in the
    > same record
    > The "-001" is the value that increments per record
    >
    > We have been trying for a week now to think of something best we could

    come
    > up with was making seperate tables for each part of the number that would

    do
    > a sort of 'if' statement:
    > IF (casetype=M) PUT M
    > IF (casedateyear=2005) PUT 05
    > THEN increment (ending+1=ending)
    >
    > I would appreciate any bit of advice anyone could give me cause I am

    stumped.
     
  3. peregenem@jetemail.net

    peregenem@jetemail.net
    Expand Collapse
    Guest

    Douglas J Steele wrote:
    > "Smart keys" (the term's slightly derogatory) aren't recommended.


    "Intelligent keys" (the non-pejorative equivalent), such as the ISBN,
    are recommended.
     
  4. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Dedren wrote:
    > How do I make a multi-part autonumber or similar incrementing unique
    > value? Example:

    ....

    I suggest you may not want to use Autonumber for that use. Autonumbers are
    designed to provide unique numbers. It in not designed to provide numbers
    in order and for a number of reasons may not do so. As a result using them
    in any application where the user sees the numbers is likely to end up with
    confusion.

    There are other ways of providing the numbers you want depending on the
    particual application.


    --
    Joseph Meehan

    Dia duit
     
  5. Dedren

    Dedren
    Expand Collapse
    Guest

    Is there any other way to generate a unique alphanumeric number automatically?

    "Douglas J Steele" wrote:

    > You don't.
    >
    > "Smart keys" (the term's slightly derogatory) aren't recommended. You're
    > trying to store 3 pieces of information: use 3 separate fields. You can
    > create a query that concatenates them together for display purposes if you
    > really need to, and use the query wherever you would otherwise have used the
    > table, but storing multiple pieces of information in a single field is
    > actually a violation of relational database rules.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Dedren" <Dedren@discussions.microsoft.com> wrote in message
    > news:230E194E-DF69-4B14-B62C-ADF670362B68@microsoft.com...
    > > How do I make a multi-part autonumber or similar incrementing unique

    > value?
    > > Example:
    > > F05-001
    > > F05-002
    > >
    > > The "F" is the type of case it is, which comes from another value in the
    > > same record
    > > The "05" is the year it was opened, which comes from another value in the
    > > same record
    > > The "-001" is the value that increments per record
    > >
    > > We have been trying for a week now to think of something best we could

    > come
    > > up with was making seperate tables for each part of the number that would

    > do
    > > a sort of 'if' statement:
    > > IF (casetype=M) PUT M
    > > IF (casedateyear=2005) PUT 05
    > > THEN increment (ending+1=ending)
    > >
    > > I would appreciate any bit of advice anyone could give me cause I am

    > stumped.
    >
    >
    >
     
  6. chriske911

    chriske911
    Expand Collapse
    Guest

    > How do I make a multi-part autonumber or similar incrementing unique value?
    > Example:
    > F05-001
    > F05-002
    >
    > The "F" is the type of case it is, which comes from another value in the
    > same record
    > The "05" is the year it was opened, which comes from another value in the
    > same record
    > The "-001" is the value that increments per record
    >
    > We have been trying for a week now to think of something best we could come
    > up with was making seperate tables for each part of the number that would do
    > a sort of 'if' statement:
    > IF (casetype=M) PUT M
    > IF (casedateyear=2005) PUT 05
    > THEN increment (ending+1=ending)
    >
    > I would appreciate any bit of advice anyone could give me cause I am stumped.


    easiest way is to write a module and let it generate a new number in
    the before update event of the record

    you can lookup the current max number from the table by using dmax or
    by keeping a follow number in a separate table and update it every time
    you generate a new number

    then use all kind of formats to concatenate them to a unique number

    grtz
     

Share This Page