Table ID

[mis]

Hi Forum,

I have noticed that in many databases that the customer/user ID is fairly long and in some cases uses a mixture of letters and numbers. My question is there a way to automatically set the customer/user ID to take in some information from the users name say (first 3 characters of the last name followed by 5 numbers).

rather than having 1 2 --------100 for customer id

 

[Mile-O]

Personally, I'd keep the Autonumber (it's faster in joined queries) as the CustomerID and have another field called CustNo or something which would hold the unique number you want.

There are many examples of creating a customer number (or similar ID) on the database if you search. I'd copy them as much as possible to obtain the desired result but just Index the field with Yes (No Duplicates) and make the autonumber your primary key.

 

[mis]

Thank you will do a search and see what i come up with.

 

[Mile-O]

Here's an example:

 

[mis]

Thanks

Hey mate,

That is great just what I wanted, took you no time at all. lol

was wondering if you wouldn't mind helping me with the query string u set up to achieve it

Left([CustNo],3) AS Prefix, Mid([CustNo],5) AS Suffix

Left([CustNo],3) AS Prefix - This is taking the first 3 char from custNo not sure what AS Prefix is

not sure what Mid([CustNo],5) AS Suffix is doing as the characters after it are numbers and if one already exists it adds a number to the end of it.

 

[Mile-O]

AS is an SQL Keyword for naming a field which may be another field, a function, or an expression. In this case I'm naming the field that will be represented by Left([CustNo],3) AS Prefix.