Results 1 to 2 of 2
  1. #1
    Steven19 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    110

    Document Control Sequence Numbering

    I'm looking at building a Document Control Database. However I need ideas of putting the tables together. Nothing has been designed yet.

    So Document Control numbers are the part I need assistance with.

    At the moment we have Prefix Letters followed by sequential numbers for lots of docs.

    Eg. HR:01, HR:02 etc for HR docs
    HS:01, HS:02 etc for Health and Safety docs
    Pol:01, Pol:02 for policies.

    Now we could at some point have someone say, oh we want documents for... Manufacturing, something we don't do, and they want the numbering to be Man:01, Man:02 and so on.



    Otherwise, I would have had a table for each Document type and it was simple. However we are expanding and at work they only have Runtime, so not easy just to start a new Table.

    So, ideas are much appreciated because I've not done anything like this. I was thinking prefix letters in one table, so they can create many many different prefix based on their requirements. However, how to sequence numbers from 1 2 3 etc for each new lettering?

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    On one hand, it reads like you don't want to create a table because of Runtime but that may be the best answer. On the other hand, it seems you're suggesting a table of document prefixes. Perhaps break your document number into fields, one field for each portion, and string it together in forms and reports. However, without a table of prefixes, anyone can make a typo and create HE instead of HR, which means you'll lose track of documents.

    As for the number, it introduces the issue of how to create it. One way is a table of numbers that are flagged as having been used thus not available, but that requires maintenance on the table. If that process fails, you can end up with non-continuous document numbers, which should not be an issue but it is often perceived to be.

    Another is to get the DMax of the number field, which raises another issue. If this is a multi-user db and 2 people begin a record on the same category, both can have the same next number, which would be bad for data if the fields are not properly indexed so as to prevent dupes. If they are, the last one to commit the record gets an error message.

    Another approach is to assign the number only when the record is saved, which means they don't know the number until it's all done. IMO, that should not matter.

    I actually built and marketed a document and record db long ago. This db even made a linkage between documents and records, which did things like prevent document obsoletion if there were associated records that had not also been obsoleted.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 1
    Last Post: 05-09-2019, 05:47 AM
  2. Replies: 7
    Last Post: 07-23-2018, 01:16 PM
  3. Replies: 0
    Last Post: 07-16-2016, 09:32 AM
  4. Replies: 5
    Last Post: 01-27-2016, 03:00 PM
  5. Replies: 3
    Last Post: 05-01-2015, 08:08 AM

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