Creating Custom Sequences

  • Thread starter Thread starter Adam Saad
  • Start date Start date
A

Adam Saad

I am creating a database that will need to use different
custom numbering sequences to track client id's, project
id's, and policy id's.

Each of these will need it's own id mask and sequencing
order. Also, Each ID will need to be a primary key for
it's table, and a foregn key in the other's table.

One of the order's is difficult because it must start the
sequencing from A to Z, then move to numbers 1 to
infinity.

I need some guidence on how to best design these
sequences. If you can provide me with any special
personal guidence and/or good online or written
references that would be much appreciated.

Thanks!

Adam Saad
Sr. Desktop Analyst
Star Title Agency, LLC
 
When dealing with complex sequence number I find it easiest to keep the
alpha charaters and numeric characters separate. This way I can perform the
sorts correctly. Then simple concatenations of the fields will generate the
complete ID when needed. You can define multiple fields as the primary key
so this won't cause a problem in that sense.

On the one where you have to go from A to Z then 1 to infinity I would use
the same idea of keeping the alphas and numbers separate. For the alpha
part for the orders that only have numbers I would set it to " Z". Notice
the space in the front. Then put equal number of spaces and Z's to be 1
longer than the longest string of characters you expect to use. Then when
you do a sort use Trim(ThisField) which will place these records at the end
of the list. Then use Trim(Left(ThisField,1)) when you need to use the
field. Replace 1 with the number of actual spaces you used.

Char Num Trim(Char) Trim(Left(Char,4) ID:Trim(Left(Char,4) & Num
ABC 15 ABC ABC ABC15
ZZZ 99 ZZZ ZZZ ZZZ99
ZZZZ 12345 ZZZZ 12345

The reason to have more Z's for the numeric ID is to cover the above
example. If you just had " ZZZ", then when you sort 12345 will come
before 99 so the order would be wrong. By having 1 more Z it forces these
records to the end.

Kelvin
 
Back
Top