Sequential numbers for each customer (1 Viewer)

[mmitchell]

I need to create a sequential number for each customer and store them in the same table. For example:

PBM0001
PKZ0001
PBM0002
PKZ0002
PKZ0003

So, the number is sequential within each customer

The number has 3 parts:
1-the prefix will always be the letter "P" then,
2-the next two letters is the customers unique abbreviation then,
3- comes the sequential number padded to four decimal places.

The table will also store the unique CustomerID.

I'm not sure how best to create and store the number. I thought that I would do some sort of Dlookup or DMax of the sequential number based on the customer ID but if I store the whole string then I don't think that I can do the DMax on it because of the letters. So, do I store the parts in seperate fields? And then will the padding of zeros mess up the DMax?

 

[FoFa]

1 - Store as separate fields and put them together when you need them
2 - Use a MAX query to return the highest entry for the current Pxxnnnnn and add 1 to it.
3 - write a function to determine it.

search this forum since this had been answered like 15000 times.

 

[mmitchell]

Here is what I have so far that I think will work once I add it to the "add new record" button on my form:

Dim strCustAbrev As String
Dim intIncrement As Integer

strCustAbrev = "KM"  '**Need to get from form.

intIncrement = Nz(DMax("Right([PatternNumber], 4)", _
                        "T_Patterns", _
                         "[PatternNumber] Like 'P" & strCustAbrev & "*'"), 0) + 1

    '**Insert the below in the new record
MsgBox "The next number is: P" & strCustAbrev & Format([intIncrement], "0000")

Thanks.

 

[Cosmos75]

Here's a quick sample I whipped up. Not pretty but I hope it helps!


EDIT: mmitchell beat me to it...

 

[mmitchell]

Thanks, I looked at your sample and it looks great.

I noticed that you seperated out the "segments" of the number, whereas my code combines them.

Note sure what the best way to store the number: combined or seperated out.

I would think that "crunching" it all at the time I right it to the databse would save CPU time later and would result in less data being tracked...but then again it really would not be any less now would it?

What are the pros and cons?

 

[Cosmos75]

mmitchell,

You can do it either way. The way I see it, you have a unique ID for a customer. And for each order (or some other transaction) you want to create a sequential number for for that transaction and tie it back to the customer, so it makes more sense to me to give each transaction a number.

Now, you do have to be carefull about some things.

1) Say you have three transactions (PBM0001, PBM0002, & PBM0003). If you delete PBM0003, and later add a new one, it will be named PBM0003, since PBM0002 is now once again the "Max". Unless you have a PBM0004, in which case the newest one becomes PBM0005 since PBM0004 is the "Max". Unless that fits your business model, I think it can become problematic to have such a transaction ID potentially being used more than once.

2) Right now you can have up to 9,999 unique numbers for each transaction; i.e. 0001 to 9999. What happens if you need more than that? It may never happen, but you do need to think if that could become a problem.