none
Auto Number Alternatives for Table Field RRS feed

  • Question

  • Hello:

    I'm creating a table with 20 fields, one of which (Field 5) is a Short Text the format YY-NNNN as explained below.

    The YY-NNNN represents the year (i.e. 19) followed by a dash and then a unique number that starts at 1 and goes to a max of 9999.  This MUST be unique for every row in the table. At a change in year, the numbers start with 1 again. The NNNN will never exceed 9999.

    To create this unique field, I will use a VBA function and a separate simple table with only two fields:  YY and the sequential number (a long integer).

    A VBA function will control this simple table, adding the next integer value to the field and updating the table.  Each year, the VBA program will insert the new two digit year and start renumbering at 1.

    I don't want to use an auto number field because the number needs to be reset at the end of the year.

    How about this alternative, and if you have a better suggestion please let me know.

    Multiple users who will be adding a row to the 20-field table will call this small VBA function that checks for a change in year, and if it's a new year, will revert the counter back to 1, update the small two field table, and return a value to the user.  If it's the same year, it will increment the table counter, and return the value to the calling program.

    I just want to make sure that this approach can never return the same YY-NNNN number if two users call the function at almost the same time.  There could be up to 10 users adding rows to the large table, and the VBA function will update the small table for each user call.

    Does this sound like a plan?

    Thanks,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Tuesday, November 12, 2019 6:13 PM

Answers

  • I don't want to use an auto number field because the number needs to be reset at the end of the year.

    How about this alternative, and if you have a better suggestion please let me know.

    Hi Rich Locus,

    I can't speak of a better suggestion, but it could be an alternative. It is the way I would work.

    In every table (except for some meta data tables) I use an Autonumber field as PrimaryKey. These are very short keys, and thus are the fastest in making the joins between records. Moreover, it becomes very simple to automaically generate SQL-strings for almost any query.

    Besides the Autonumber PK you can add a next field for your YY-NNNN number. This field can be made unique, and have an alternate or secundary key for sorting or selection purposes.

    To prevent that during the editing of a new record, that a second person uses the same YY-NNNN number, you could first create a new record with Autonumber and YY-NNNN number, store it, and after that start the editing of the remainder of the fields.

    Another way could be to make an unbound form for new records, and fill in the YY-NNNN number on saving the record.

    Or, when you want to use a bound form, take the first available YY-NNNN number, and adjust this - if necessary - on the moment of saving.

    Finally, in the case that something nevertheless goes wrong, you can change the YY-NNNN number "by hand" afterwards.

    Imb.


    • Edited by Imb-hb Tuesday, November 12, 2019 7:49 PM or selection
    • Marked as answer by RichLocus Tuesday, November 12, 2019 9:25 PM
    Tuesday, November 12, 2019 7:05 PM
  • It sounds like trouble to me. And it will get complicated. The more users you have, the more checking and record-locking you will have to do.

    This breaks Normal Form in that each field is holding two elements, the year and a serial number. And saying the number will "never" be higher than 9999 is tempting fate. Just like in the old days of COBOL when programmers used 2 digit years instead of 4 thinking their software would never be in use long enough to get to the year 2000


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by RichLocus Tuesday, November 12, 2019 9:25 PM
    Tuesday, November 12, 2019 9:05 PM
  • And saying the number will "never" be higher than 9999 is tempting fate.

    Hi Rich Locus,

    In all my applications I (almost) never, never use any coding system. Sooner or later you will experience that the used systematics fail, as Bill also concluded.

    Why not just a field for the year, and another field for the 'ever continuing' sequencenumber. Why must the sequencenumber be reset to 1 in a new year when you can select on the Year?

    Imb.

    • Marked as answer by RichLocus Friday, November 15, 2019 1:12 AM
    Tuesday, November 12, 2019 10:20 PM
  • At a change in year, the numbers start with 1 again.

    You might like to take a look at CustomNumber.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file includes a method for sequentially numbering rows per group.  In the demo the group is gender, in your case it would be the year.  The year and sequential number should each be stored in a separate column.  The two columns should either be made the composite primary key of the table, or included in a single unique index.

    The method employed to generate the numbers is a development of that originally popularised by Roger Carlson for generating a single set of sequential numbers, and caters for possible conflicts in a multi-user environment by trapping the key/index violation and incrementing the number until no violation occurs.


    Ken Sheridan, Stafford, England

    • Marked as answer by RichLocus Friday, November 15, 2019 1:13 AM
    Wednesday, November 13, 2019 12:03 AM

All replies

  • I don't want to use an auto number field because the number needs to be reset at the end of the year.

    How about this alternative, and if you have a better suggestion please let me know.

    Hi Rich Locus,

    I can't speak of a better suggestion, but it could be an alternative. It is the way I would work.

    In every table (except for some meta data tables) I use an Autonumber field as PrimaryKey. These are very short keys, and thus are the fastest in making the joins between records. Moreover, it becomes very simple to automaically generate SQL-strings for almost any query.

    Besides the Autonumber PK you can add a next field for your YY-NNNN number. This field can be made unique, and have an alternate or secundary key for sorting or selection purposes.

    To prevent that during the editing of a new record, that a second person uses the same YY-NNNN number, you could first create a new record with Autonumber and YY-NNNN number, store it, and after that start the editing of the remainder of the fields.

    Another way could be to make an unbound form for new records, and fill in the YY-NNNN number on saving the record.

    Or, when you want to use a bound form, take the first available YY-NNNN number, and adjust this - if necessary - on the moment of saving.

    Finally, in the case that something nevertheless goes wrong, you can change the YY-NNNN number "by hand" afterwards.

    Imb.


    • Edited by Imb-hb Tuesday, November 12, 2019 7:49 PM or selection
    • Marked as answer by RichLocus Tuesday, November 12, 2019 9:25 PM
    Tuesday, November 12, 2019 7:05 PM
  • It sounds like trouble to me. And it will get complicated. The more users you have, the more checking and record-locking you will have to do.

    This breaks Normal Form in that each field is holding two elements, the year and a serial number. And saying the number will "never" be higher than 9999 is tempting fate. Just like in the old days of COBOL when programmers used 2 digit years instead of 4 thinking their software would never be in use long enough to get to the year 2000


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by RichLocus Tuesday, November 12, 2019 9:25 PM
    Tuesday, November 12, 2019 9:05 PM
  • Bill and Imb:

    I will use suggestions from both of you.  I will put in record locking when I update the table that only contains Autonumber as the main key, Year and sequence number.  Also, I will update the small table containing the number before I do the edit on the larger table.

    Thanks,



    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Tuesday, November 12, 2019 9:30 PM
  • And saying the number will "never" be higher than 9999 is tempting fate.

    Hi Rich Locus,

    In all my applications I (almost) never, never use any coding system. Sooner or later you will experience that the used systematics fail, as Bill also concluded.

    Why not just a field for the year, and another field for the 'ever continuing' sequencenumber. Why must the sequencenumber be reset to 1 in a new year when you can select on the Year?

    Imb.

    • Marked as answer by RichLocus Friday, November 15, 2019 1:12 AM
    Tuesday, November 12, 2019 10:20 PM
  • At a change in year, the numbers start with 1 again.

    You might like to take a look at CustomNumber.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file includes a method for sequentially numbering rows per group.  In the demo the group is gender, in your case it would be the year.  The year and sequential number should each be stored in a separate column.  The two columns should either be made the composite primary key of the table, or included in a single unique index.

    The method employed to generate the numbers is a development of that originally popularised by Roger Carlson for generating a single set of sequential numbers, and caters for possible conflicts in a multi-user environment by trapping the key/index violation and incrementing the number until no violation occurs.


    Ken Sheridan, Stafford, England

    • Marked as answer by RichLocus Friday, November 15, 2019 1:13 AM
    Wednesday, November 13, 2019 12:03 AM
  • The other thing you will have to guard against, and which you omitted to mention, is gaps in your sequence.

    You will probably not want to end up with 19-0123 and then 19-0125 with 19-0124 missing, especially if your application is subject to auditing. The auditors will be sure to ask "what happened to 19-0124?

    The above can happen, if someone fails to complete an update or does an Undo or maybe switches their computer off or the network crashes.


    Wednesday, November 13, 2019 1:01 AM
  • This is a working method....we have used this technique extensively in my previous job to mark the orders.

    So as you said you will have a table that holds E.g

    Year           NumberOfOrders

    2018              2052

    2019              1879

    So for the current year 2019 you have till now 1879 orders...on the next order the VBA will read this table ...finds out the NumberOrOrders ...increment the NumberOfOrders to 1880 ...write the value back to the Orders table and that's about all

    You keep the Autonumber as a generic Primary Key in the Orders Table and just on each new Record you will insert the combined key

    e.g

    OrderPK          OrderCombinedKey      .... Rest Fields

    24234                  2019-1877

    24235                  2019-1878

    24236                  2019-1879

    24237                  2019-1880

    Wednesday, November 13, 2019 8:37 AM