Results 1 to 4 of 4
  1. #1
    PCData is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2020
    Posts
    2

    Sequential numbering in a table

    Hi,

    This is my first post so please excuse my ignorance if I'm calling something the wrong name.

    I have been managing a large Database for the past 7 years, with very little knowledge of the workings of a Database(but I have picked up a lot along the way).

    At the end of each year I simply tae a copy of the current years database, remove all the data and reformat some of the fields to create a new years database.

    I have a commitment table that holds a unique ID for each record. The format of this is a 6 digit number and I simply change the first digit at the stat of each year in the table design.
    My problem is that now we are in year 7 we are about to go over the Commitment ID 799999 and I'm not sure what to do to fix it.

    Is there a way of making the database forget that it as used the previous yeas numbers so that when the first commitments added for the new year it will appear as 70001?

    Thank in advance for ay help.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    your explanation is a bit vague to provide a possible solution-

    1. is the 99999 part a 'count' accruing for the last 7 years, or a count for the current year? (looks like the latter, but to be clear)
    2. Your ID is unique - but is it the primary key? (i.e. the value other tables reference) or something like a invoice number?
    3. what is the datatype for your commitmentID? long? autonumber?
    4. what do you plan to do when you reach year 10?

    If you are clearing down the database each year, then access will have lost track of entries deleted - and the unique ID is preceded with a 7 so 112345 is different from 712345 anyway

  3. #3
    PCData is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2020
    Posts
    2
    Hi Ajax,

    My current year is at commitment 697512 and it started at 689612 so this has been rolling from the last 6 years.
    The Commitment ID is the primary key.
    It is an AutoNumber.
    I have been promised that we will be moving to another system within the next 2 years so hopefully I don't need to worry about what happens when I reach year 10 (fingers crossed)

    What I want is the first commitment of year 7 to be 700001.

    Thanks

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    OK

    based on what you say here 'I simply change the first digit at the stat of each year in the table design' sounds like your number is made up of two fields - a 'start' field (i.e. 600000) and an autonumber field which are added together to create 689612 as your first number for year 6. Is this correct?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Sequential numbering help
    By mtmtfranz in forum Macros
    Replies: 1
    Last Post: 09-30-2019, 08:45 AM
  2. Sequential Numbering
    By thegrimmerdiscovery in forum Access
    Replies: 3
    Last Post: 07-16-2019, 06:15 AM
  3. sequential numbering with criteria
    By Harmoesh in forum Queries
    Replies: 1
    Last Post: 07-04-2018, 05:16 PM
  4. Sequential Numbering in a Form
    By Falafa in forum Forms
    Replies: 6
    Last Post: 10-05-2012, 08:20 AM
  5. Help with Sequential Numbering
    By orion in forum Programming
    Replies: 3
    Last Post: 07-06-2009, 01:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums