soccerl
asked on
auto generating Customer ID
Hi,
I'm working on a VB project and need to generate Customer ID based on the Customer last name and position(record position). But I don't want the Customer ID to go beyond 10 characters. It should permanantly be within 10 characters. How can I do this?
Thanx.
I'm working on a VB project and need to generate Customer ID based on the Customer last name and position(record position). But I don't want the Customer ID to go beyond 10 characters. It should permanantly be within 10 characters. How can I do this?
Thanx.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanx 4 all ur responses. But what my client wants is a CustomerID of this nature: <First 3 characters of Customer last name> + <position> eg: If the customer is John and at third position then, his ID would be: JOH3.
I've already done this this, and works fine. But, my worry is that as the number of customers keep growing, the ID will finally go beyond 10 characters.
For bhagyesht and IeuanJ, u're ideas so good but the ID will keep growing to even beyond characters b'se of Count(*) or intPos.
What I need is a Unique Customer ID that will never go beyond 10 characters.I hope this can be achieved if I ignore Count(*) or record counting.
I need ur advice.
Thanx.
I've already done this this, and works fine. But, my worry is that as the number of customers keep growing, the ID will finally go beyond 10 characters.
For bhagyesht and IeuanJ, u're ideas so good but the ID will keep growing to even beyond characters b'se of Count(*) or intPos.
What I need is a Unique Customer ID that will never go beyond 10 characters.I hope this can be achieved if I ignore Count(*) or record counting.
I need ur advice.
Thanx.
I would add a date field to your database to record when the customer has registered, then do the following (The SQL below is pseudocode, im sure you can work out the specifics).
--------------
SELECT max(dateregistered) from customers
select position from customers where dateregistered = max(dateregistered)
if position >= 9999999 Then
newPosition = 1
else
newPosition = position + 1
End If
--------------
That position will loop back around to 1 after reaching 9999999 therefor never becoming longer than 7 digits. Customer 1 will probably be long gon by now unless this is for a bank or gas board type thing.
Now all you need to do to create the ID is :
--------------
customerID = left(strName,3) & CStr(newPosition)
--------------
--------------
SELECT max(dateregistered) from customers
select position from customers where dateregistered = max(dateregistered)
if position >= 9999999 Then
newPosition = 1
else
newPosition = position + 1
End If
--------------
That position will loop back around to 1 after reaching 9999999 therefor never becoming longer than 7 digits. Customer 1 will probably be long gon by now unless this is for a bank or gas board type thing.
Now all you need to do to create the ID is :
--------------
customerID = left(strName,3) & CStr(newPosition)
--------------
well IeuanJ thats not gonna be unique if it loops back.
Anyway what is 9999999 persons having the starting 3 characters of the last name same? if you are designing applications for such huge volume of data then y are you restricting to 10 characters?
if you still want to do that then one more way is to write your own function which will take a number and return you a character like say
A for 1
B for 2
and so on till
Z for 26
once u have done that then u can have the number of character as 9999999*2.6
still want more?
a for 27
b for 28
c for 29
so on till
z for 52
once u have done that then u can have the number of character as 9999999*5.2
still need more?
use the other signs as well, but still I would say its better to use 11 or 12 digits and so on.
Anyway what is 9999999 persons having the starting 3 characters of the last name same? if you are designing applications for such huge volume of data then y are you restricting to 10 characters?
if you still want to do that then one more way is to write your own function which will take a number and return you a character like say
A for 1
B for 2
and so on till
Z for 26
once u have done that then u can have the number of character as 9999999*2.6
still want more?
a for 27
b for 28
c for 29
so on till
z for 52
once u have done that then u can have the number of character as 9999999*5.2
still need more?
use the other signs as well, but still I would say its better to use 11 or 12 digits and so on.
bhagyest there is no such thinig as a unique id, especially if you are going to limit the number of digits involved. though after 10 million customers I think its reasonable to overwrite from the beginning dont you ?
Lets face it an application for more customers than that would not have a 10 digit restriction.
Lets face it an application for more customers than that would not have a 10 digit restriction.
sorry leuanJ the message's first line was for you the rest was for our friend who asked the question. I have no objections execpt that the fact that you in the code wrote that it should loop back which would lead to problems so the message was just to increase the number of alailable records. Not for you.
ASKER
Thanx guyz. U're ideas are so good, but I have to absorb my client's requirements too.
I have this idea: Suppose, I include the Year, and Month in Customer ID like this: <First 3 chars of Lastname>+<2 year's digits>+<1 month's digit>+<k> ;k is the record position/number. eg: for John registered in March 2003 would have ID: JOH033k (k = 1,2...n)
To restrict the above ID from growing, I would reset k =1 whenever it turns 1st of the next month. so that k would always reflect the number of customers in a month. I hope this would always generate a Unique ID and I believe my client wouldn't mind atmost 15 characters for cases when customers' number increase for some month.
But, I haven't yet come up with afunction that accomplishes the this. I need ur assistance.
Thanx for your continued support.
I have this idea: Suppose, I include the Year, and Month in Customer ID like this: <First 3 chars of Lastname>+<2 year's digits>+<1 month's digit>+<k> ;k is the record position/number. eg: for John registered in March 2003 would have ID: JOH033k (k = 1,2...n)
To restrict the above ID from growing, I would reset k =1 whenever it turns 1st of the next month. so that k would always reflect the number of customers in a month. I hope this would always generate a Unique ID and I believe my client wouldn't mind atmost 15 characters for cases when customers' number increase for some month.
But, I haven't yet come up with afunction that accomplishes the this. I need ur assistance.
Thanx for your continued support.
cant you just use year (y) month (m) day (d) count (c) as follows YYYYMMDDCC, thats 10 digits and unles you get 100 customers a day should work out, even if you do you can use letters as well in the count to incerase the number to 36*36 (cant be bothered to work it out). Your method would work but only if you use two digits for the month, or perhaps use 123456789ABC with ABC being months 10,11 & 12.
ASKER
Thanx IeuanJ , but how can I make CC (counter) always start from 1 whenever a month ends.
soccerl:
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.
Split between IeuanJ and bhagyesht
If you are using Oracle you can create a sequence and use it as above.