locked
Autonumbering RRS feed

  • Question

  • I would like to have a field autonumber with a unique numbering scheme:

    Number would be:

    M-STDPT-PXXX 

    H-STDPT-PXXX

    or

    M-STDPT-AXXX

    H-STDPT-AXXX

    The XXX would auto increment as required. So the M and H are variable as well as the P or A and would be selected by the user to build the number then the XXX would increment depending on the variables selected.

    Tuesday, September 16, 2014 4:26 PM

Answers

  • The XXX would auto increment as required. So the M and H are variable as well as the P or A and would be selected by the user to build the number then the XXX would increment depending on the variables selected.

    Hi minitel,

    When it was my problem, I would probably store the information in three fields: Part1, Part2, and Seq_nr.

    Part1 can have values "M", "H", ...
    Part2 can have values "P", "A", ...

    The next Seq_nr for Part1 = 'M' and Part2 = 'P' can easily be found, using

      next_nr = NZ(DMax("Seq_nr",<domain>,"Part1 = 'M' And Part2 = 'P'")) + 1

    For display purposes in forms or reports you can concatenate the values:

      total_code = Part1 & "-STDPT-" & Part2 & Format("000",Seq_nr)

    Imb.

    Tuesday, September 16, 2014 7:45 PM
  • I would not do this since it breaks the rules of normalization. You have meaningful information hidden in this single column.

    Keep the components of this composite field in separate columns and combine them whenever needed for display purposes. This will also make it possible to query on the separate components whenever needed.

    Also bear in mind that any autonumbering solution using MAX/DMAX may not work reliably in a multi-user environment since multiple users could receive the same number.


    Tuesday, September 16, 2014 8:32 PM
  • It would be nice if the scenario you describe would work but as 'allthegoodnames are taken' suggests it would break the rules of normalization in a single database. I have a "switchboard" where the user can select different databases that have been set up so I could make one for STDPT-AXXX and one for STDPT-PXXX. unless there is another way to do it in a single database, notwithstanding teh normalization problem.

    Hi minitel,

    Is it a problem to store the "Autonumber" in two fields. One field for the user's choice "STDPT-A" or "STDPT-P", and the other field with the sequence number XXX?

    If you insists on using one field to store the whole "Autonumber", then you can find the different next sequence numbers by using the DMax function with the condition Left(Autonumber,7) = "STDPT-A", viz. Left(Autonumber,7) = "STDPT-P".
    Then you do some string manipulation to split off the last three characters, increment this by 1, and concatenate it again.

    As an alternative you can even do some string calculation.

    Imb.

    Wednesday, September 17, 2014 2:19 PM

All replies

  • The XXX would auto increment as required. So the M and H are variable as well as the P or A and would be selected by the user to build the number then the XXX would increment depending on the variables selected.

    Hi minitel,

    When it was my problem, I would probably store the information in three fields: Part1, Part2, and Seq_nr.

    Part1 can have values "M", "H", ...
    Part2 can have values "P", "A", ...

    The next Seq_nr for Part1 = 'M' and Part2 = 'P' can easily be found, using

      next_nr = NZ(DMax("Seq_nr",<domain>,"Part1 = 'M' And Part2 = 'P'")) + 1

    For display purposes in forms or reports you can concatenate the values:

      total_code = Part1 & "-STDPT-" & Part2 & Format("000",Seq_nr)

    Imb.

    Tuesday, September 16, 2014 7:45 PM
  • Hi,

    When you said the XXX part would auto increment, are you saying that it would start with 1 and count up no matter whether the record is an M-STDPT... or a H-STDPT...? Or, did you want each category to have their own incrementing sequence? For example:

    M-STDPT-P001
    M-STDPT-P002
    M-STDPT-A001
    H-STDPT-A001
    H-STDPT-A002
    etc...

    Tuesday, September 16, 2014 8:20 PM
  • I would not do this since it breaks the rules of normalization. You have meaningful information hidden in this single column.

    Keep the components of this composite field in separate columns and combine them whenever needed for display purposes. This will also make it possible to query on the separate components whenever needed.

    Also bear in mind that any autonumbering solution using MAX/DMAX may not work reliably in a multi-user environment since multiple users could receive the same number.


    Tuesday, September 16, 2014 8:32 PM
  • Also bear in mind that any autonumbering solution using MAX/DMAX may not work reliably in a multi-user environment since multiple users could receive the same number.

    Hi ATGNWT,

    Not so much when you postpone the execution of DMax until storage of the record.

    Anyway, with very heavy traffic, you can make a loop with increasing autonumbers until successful.

    Imb.

    Tuesday, September 16, 2014 9:15 PM
  • Thanks for all the responses.

    It would be nice if the scenario you describe would work but as 'allthegoodnames are taken' suggests it would break the rules of normalization in a single database. I have a "switchboard" where the user can select different databases that have been set up so I could make one for STDPT-AXXX and one for STDPT-PXXX. unless there is another way to do it in a single database, notwithstanding teh normalization problem.

    Wednesday, September 17, 2014 1:52 PM
  • It would be nice if the scenario you describe would work but as 'allthegoodnames are taken' suggests it would break the rules of normalization in a single database. I have a "switchboard" where the user can select different databases that have been set up so I could make one for STDPT-AXXX and one for STDPT-PXXX. unless there is another way to do it in a single database, notwithstanding teh normalization problem.

    Hi minitel,

    Is it a problem to store the "Autonumber" in two fields. One field for the user's choice "STDPT-A" or "STDPT-P", and the other field with the sequence number XXX?

    If you insists on using one field to store the whole "Autonumber", then you can find the different next sequence numbers by using the DMax function with the condition Left(Autonumber,7) = "STDPT-A", viz. Left(Autonumber,7) = "STDPT-P".
    Then you do some string manipulation to split off the last three characters, increment this by 1, and concatenate it again.

    As an alternative you can even do some string calculation.

    Imb.

    Wednesday, September 17, 2014 2:19 PM