genarating dynamic employee id

Question

User2112741310 posted

hi guys,

i am using ms access 2003 database

and i want to generate different employee id's 


like for manager starts from mm0001

and increasing automatically by one


and for consultant starts from mc00001

and increasing by one automatically on registration of new consultant


plz help me how to do this

Answer 1

User-821857111 posted

I would store assigned numbers in a separate column along with another column that keeps the type (mc, mm etc):

EmployeeType TEXT
AssignedNumbers NUMBER

then you can get the next number for managers with this type of query:

SELECT MAX(AssignedNumbers)+ 1 As NewID FROM YourTable WHERE EmployeeType = 'mm'

You need to check how many digits are in the number before constructing your Identity as a string.

 

Answer 2

User3866881 posted

HI：）
As far as I know, the auto increament Id should be done with numeric type (like int or Long). As for a string type, I'm afraid you cannot do that in SQL directly.

However, if you want to show it, you can do!

if you want to show Employees' Ids onto the GridView, Just do this in the <ItemTemplate>——

<%#Eval("Id","mm{0:0000}")%>

and the other is——

<%#Eval("Id","mc{0:0000}")%>

Answer 3

User1867929564 posted

It would be nice if you show your table structure and few sample data.
It mean that before inserting we should know wht is the designation of employee.
Secondly if t'row,if designation of employee change then will its empid change ?
Seeing table structure is nesseccary.

anyway in front end also you can write function for it which accept designation as parameter(or whtever column name)

refer this,

http://stackoverflow.com/questions/2570728/how-to-auto-generate-an-alpha-numeric-id-in-c-using-ms-access-database

another way,
Set the DefaultValue property to your personal ID-creator-function.
This function must be defined in a module like this

public function MyIDCreator(<params>) as string
'create your ID here

MyIDCreator = <created string>
end function

The use
DefaultValue=MyIDCreator()
in your ID field of the table.

whichever way you go,share your your problem

 

Answer 4

User-821857111 posted

another way,
Set the DefaultValue property to your personal ID-creator-function.
This function must be defined in a module like this

Are you suggesting creating a module in Access? You cannot call modules, macros, forms etc from Access using the JET provider. And you certainly don't want to be trying to invoke an Access Application on the web server.