Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Posts
    121

    Question Unanswered: Employee ID field

    To all....

    I have a need to create a unique number for temporary service associates who work in our building, or in one of our other DC's.

    I want to create a form that would have a drop down list with the 2 letter abbreviation for each of our buildings, the have a field that would begin with the letters TK, then the 2 digit abbreviation for the building followed by a unique 5 digit number that the system creates...

    Example - TKBS00001

    This would be created as the form was opened and then the first and last name supplied by the user and then all 3 fields stored once saved.

    Any easy suggestions???


  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This design is not normalized. A unique identifier should not contain information! I suggest an autonumber and then when needed you can make the "ID" appear that way on screen, based on their location.

    Eg. Employee number 956 works in building "BS"
    Me.txtEmpID.value = "TK" & Building_ID & Employee_Number
    Or whatever
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    are you allocating numbers centrally of locally.. is it one db, or does each site / each building have its own db

    if 'all' you want is a unique number then 'just' use an autogenerated number
    on reports and elsewhere prefix that autogenerated number with the site / building / wahtever data element

    the drawback of this approach is that you will not have contiguous numbers for eachs site / building.

    Im not sure its a problem.. but it may be to your user base if they want to see
    TKBS00001
    TKBS00002
    TKBS00003
    TKZQ00001
    TKZQ00002

    as opposed to what Id go for which could be...

    TKBS00001
    TKZQ00002
    TKBS00003
    TKZQ00004
    in reality you would have
    EmpId an autogen filed PK
    SiteID say text (2)
    BuildingID say text (2)
    a compound index SiteID | BuildID | EmpID

    you would need to put a bit of code (probably in a function in a common module available to all forms, reports & queries) behind all search functions to strip off the redundant alpha characters. you might need another fuicntion to explode the 9 digit empref into its 3 components... might be better to do that as a class with 3 methods. Effectively your app looks for the EmpID to find a vlaue, whereas the users will see it as the compound value. you have to do a bit more work but they see what they want, you use an auotincrement to allocate the ID.

    If you must have contiguous numbers for each site/buildiong then alternative is to find the current highest number for each site/building as required. That will require table locks, and a lot more design work.

    There are going to be purists who will say that you should never allow such an abomination, you shouldn't use alphas in primary keys, but if thats what the customer wants......

    If your users 'insists' on contiguous numbers then Id check why they want that.. after all if they are temporary employees then by defintion they are transient, if they need contiguity get them to justify, if they want to know who is on site to day.. then keep an issue number, or generate one on the day (eg COUNT for a specific site/building element)
    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Another 'benefit' : If an employee changes his location, so does his "EmployeeID"

    Just stick to using an Autonumber though...
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •