auto generating Customer ID


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?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You can have a PARAM (a parameter) table having a single row only, where you can store incremented nos or autonumbers and on runtime club this autonumber(some fixed no. of digits) with fixed no. of characters to form the CustomerID.

If you are using Oracle you can create a sequence and use it as above.
u can use the first 3 or 4 characters of the customer's last name and add to it the number of customers having the last 3 or 4 characters same. the rest of the charactes could be a serial number.
your query could be

SELECT Count(*)+1 AS newSrNo FROM <TableName> WHERE <lastname> Like "<first3/4characters>* "

like if your last name is johnson then your query could be
SELECT Count(*)+1 AS newSrNo FROM <TableName> WHERE <lastname> Like "joh*"
SELECT Count(*)+1 AS newSrNo FROM <TableName> WHERE <lastname> Like "john*

what ever value u get just add it to "joh" or "john" and that will be ur unique key

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
assuming you have already extracted the name into a variable calles strName and the position into a variable called intPosition ....

dim intTemp as integer
intTemp = Len(CStr(intPosition))
if len(strName) > (10 - intTemp)
  strName=left(strName,(10 - intTemp))
End If
strCustomerId = strName & CStr(intPosition)

strCustomerId will now contain a 10 charachter or less string with the last x digits being the position and the rest filled out by as much of the name a possible.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

soccerlAuthor Commented:
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.

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
    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.
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.
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.
soccerlAuthor Commented:
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.
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.
soccerlAuthor Commented:
Thanx IeuanJ , but how can I make CC (counter) always start from 1 whenever a month ends.
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 
Post your closing recommendations!  No comment means you don't care.
Split between IeuanJ  and bhagyesht
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.