locked
genarating dynamic employee id RRS feed

  • 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

    Monday, May 2, 2011 3:59 AM

Answers

  • 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.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 2, 2011 6:30 AM

All replies

  • 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.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 2, 2011 6:30 AM
  • 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}")%>

    Tuesday, May 3, 2011 10:31 PM
  • 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

     

    Wednesday, May 4, 2011 5:36 AM
  • 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.

     

    Wednesday, May 4, 2011 9:55 AM