Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Fairmont, West Virginia, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counter/sequencing

    I have a requirement (in Access 2000) to automatically sequence items with a prefix referencing a system (such as SYS1) followed by a dash then a three-digit sequence. The field then will be used in reports and for tracking purposes. I have at least four different prefix requirements at the moment, and want each to have its independent sequence range.
    I think this can be done with separate tables holding only the sequence number, retrieving the value, adding 1 to it, formatting to the desired 3-digit format, appending the appropriate prefix for the specific 'system', storing the value as a string in a certain field in the record, then resaving the newly incremented value back to the 'number' table.
    I believe its another of those "I know it can be done, but can't figure it out on my own" problems.
    Any and all help is greatly appreciated - thanks in advance

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counter/sequencing

    Why not filter the table containing your active records (eg SYS1-*) and sort in descending order and display only the first record. This gives you the highest 'number' in use for this prefix. Strip the prefix off to give you the number, then increment it for the next in sequence.
    One point to consider. How often are records added and how many user will work on the system at the same time. In other words is there a chance of duplicating this key? That problem has to be addressed.

Posting Permissions

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