Results 1 to 2 of 2
  1. #1
    Skippy19 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    1

    Auto Number Assignment

    Please help me I am completely lost. I need access to assign the next available number based on criteria entered.



    I am trying to assign numbers to some documents. Each document will have a number set up like AA - BB - CC - D (SEE CHART BELOW). The "AA" will be based on what document type it is (I have a look up for this), the "BB" will be based on what project it is (I also have a look up for this). The "CC" is where I need help, I need it to assign the next number in the series,

    so ex:

    31 - 12 - 22
    31 - 12 - 23
    31 - 12 - 24


    Click image for larger version. 

Name:	Capture.JPG 
Views:	13 
Size:	22.7 KB 
ID:	39757

    Tables:

    • Document Type
    • Project Codes
    • Document List


    Click image for larger version. 

Name:	1.JPG 
Views:	12 
Size:	18.5 KB 
ID:	39758

    Click image for larger version. 

Name:	Capture2.JPG 
Views:	12 
Size:	11.2 KB 
ID:	39759
    I am not sure what makes the most sense a Query, or formula in the table or what! Sorry I am very new so I probably didn't verbalize this well.

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Numbering sequence is based on on project AND document type? Like 01-01-01-..., 01-01-02-..., 01-02-01-..., 02-01-01-..., etc!
    Or numbering sequence is based on on project only? Like 01-01-01-..., 01-01-02-..., 01-02-03-..., 02-01-01-..., etc!
    Or you have a single numbering sequence? Like 01-01-01-..., 01-01-02-..., 01-02-03-..., 02-01-04-..., etc!

    And what about variant part?. Do you have several variants for same document number, or there can be only one?

    Generally, as you already have all other parts of full number in your table as separate field, make numeric part also a separate field, and create a composed unique index based on those fields instead of full number. Whenever you need to show or print the full number, simply compose it. No need to save it into table!. And as you obviously don't do any math with any of components of full number, make them all strings with leading zeros.

    To get a new number part of full number, simply query the table for max value of number part where other parts of unique index of full number are same, convert it to number, add 1, convert it back to string (with leading zeros, when number of numeric characters is less than your format foresees, and then create a new entry.

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

Similar Threads

  1. Replies: 10
    Last Post: 04-01-2018, 07:45 AM
  2. Replies: 2
    Last Post: 12-29-2016, 11:42 AM
  3. Replies: 3
    Last Post: 09-09-2015, 11:26 AM
  4. Replies: 5
    Last Post: 11-30-2014, 12:46 PM
  5. Auto number automatically next number
    By wnicole in forum Access
    Replies: 3
    Last Post: 10-17-2013, 08:45 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