how to generate employee code

[uttamsaxena]

I have to generate unique code for each person in an Employee table. This code will not be the primary key for this table. Primary key is an auto number. This Unique code is for refering to each person in all the correspondence/document/everywhere and every person will know his code and refer in all the correspondance.

I want to make a column in this table for this code. There can be two criteria for this code generation--

1. The code will be First alphabet of first name followed by sr no for that letter in 4 digits. Say Mr. Andrew is 99th person with alphbet A then his code will be A0099. Or Miss Jany is first person with letter J, her code will be J0001.

OR

2. The code will be a five digit 7 digit number--of which the first 4 digits are year of joining in organisation and remaining 3 digits are joining serial number of this person in that year.Say Mr. Andrew is 12th person who joined in 1996 then his code will be 1996012. Or Miss Jany is 118th person who joined in 2000, her code will be 2000118. For this I will add two columns namely Joining Year and SrNo in this Table.

I want the Access, by some means to generate this code based on the above critaria in one table.Can you people help me with the ideas about achieving this task. Presently we are manually assigning these codes.

 

[KenHigg]

If I may blow a hole in your idea - I would stay away from building any intelligence into the employee number and use a seeded autonumber.

 

[ByteMyzer]

Nonsense, there is no good reason that this can not be done. The following function should do what you want:

Public Function ECode( _
    ByVal strTable As String, _
    ByVal strField As String, _
    ByVal strName As String) _
    As String

    Dim rs As DAO.Recordset
    Dim lNo As Long

    Set rs = DBEngine(0)(0).OpenRecordset("SELECT COUNT(*) " _
        & "FROM [" & strTable & "] " _
        & "WHERE [" & strTable & "].[" & strField & "] IS NOT NULL", _
        dbOpenSnapshot)
    lNo = Nz(rs(0), 0)
    rs.Close
    Set rs = Nothing

    ECode = UCase(Left(strName, 1)) & Format(lNo + 1, "0000")

End Function

Assume the following - A table named MyEmployeeTable, with a field named EmployeeCode. If you have 37 records in the table for which the employees' first names all begin with "M", and you want to obtain a code for an employee named Michael Smith, for whom there is currently no existing code. Calling the function thus:

?ECode("MyEmployeeTable","EmployeeCode","Michael Smith")

...should return the code: M0038.

See if this solution works for you.

 

[KenHigg]

Nonsense, there is no good reason that this can not be done.

I didn't say that it couldn't be done. Just that, from my experience, building this type of logic into an id number rarely yields any sustainable benefit considering the over head.

For example; Say you put a 'J' in front because it's the initial of their first name. What good is it? Or what good does it do anyone to know that this was the 99th person with a 'J' as the first letter of their first name?

 

[ByteMyzer]

Very true, you did not say that it couldn't be done. But you did discourage the idea instead of answering the question.

However, uttamsaxena's question was not whether it was a good idea; uttamsaxena's question had to do with how it could be done, and that was the question I answered.

"Rule 1: The customer is always right. Rule 2: If the customer is wrong, see Rule 1."

 

[RuralGuy]

ByteMyzer,
Many posters are here simply to tap into the wealth of knowledge and experience of those who answer questions. The new posters often simply ask the wrong question and many responders read in-between the lines and answer the question that probably should have been asked or attempt to determine if the OP has looked at the problem with the proper perspective. It is especially noticable with normalization issues but other topics are suspect as well. IMHO Ken was wise to discourage the OP's direction but as with all suggestions, the OP can accept it or not as they choose. You were also correct in supplying a possible solution for the OP and now the OP has two ideas for the price of one.

 

[ByteMyzer]

RuralGuy,

While it is true that new posters often ask the wrong question, they do not always ask the wrong question. When I read uttamsaxena's original post in this thread, I gathered that this was a code stipulation posed by his employer, as I have often observed to be the case when providing a bespoke solution to a client. I did not gather that the poster did not know what he was talking about.

I am quite certain that KenHigg did not intend to slight or put down uttamsaxena in any way; I simply wanted to point out that there was a solution for the question that was asked, and that we should not be so ready to so dismiss someone's idea with "I would stay away from..." without first asking something along the lines of "Are you sure this is the approach you want to take, are you open to an alternative or is this something your boss is telling you to do?"

I myself have been on the other end of the deal, asking how something could be done, and receiving a response along the lines of "It's a bad idea" without a solution that met my needs. It's frustrating. Again, I am sure that that was not KenHigg's intention, but it could be read that way.

I don't mean to put anyone down by any of this. I've been a member of this forum for some time, and have seen some excellent questions, comments and solutions posted here. I just don't think it's a good idea to give a response that, upon the first reading, can give a discouraging impression.

Finally, I am not saying that I am guiltless in any of this. In this instance, while KenHigg's suggestion of using a seeded autonumber was not a solution that answered the problem, it is still a useful alternative. As you stated, uttamsaxena now has two methods from which to choose, and likewise I should not have so quickly dismissed KenHiggs' response.

I realize that this post is rather drawn out, but I wanted to be sure that I was clear as to where I was coming from on this.

 

[gemma-the-husky]

I can see why uttamsaxena may need the employee code. you probably want to do something similar to what bytemizer said.

have a table with 26 rows, and two columns - one called code letter, and one called next ref. then when you get a new employee, read the next ref for the appropriate letter, and update it for the next use. You may get holes occasionally if you read a number, and then scrap your edit, but you will end up a number sequence for each letter.

to be absolutely sure that this is watertight, this is sometimes implemented by locking the lookup table in the be database while the ref is retrieved.
try looking for nextautonumber as a search reference, and modify whatever you find to suit.

 

[gemma-the-husky]

although as Ken says, if your company just accepted that employees could now have a numeric reference say employee no 58 instead of ref J12, it would make life a lot easier for the programmer.

 

[KenHigg]

I can see why uttamsaxena may need the employee code. you probably want to do something similar to what bytemizer said.

have a table with 26 rows, and two columns - one called code letter, and one called next ref. then when you get a new employee, read the next ref for the appropriate letter, and update it for the next use. You may get holes occasionally if you read a number, and then scrap your edit, but you will end up a number sequence for each letter.

to be absolutely sure that this is watertight, this is sometimes implemented by locking the lookup table in the be database while the ref is retrieved.
try looking for nextautonumber as a search reference, and modify whatever you find to suit.

So what does the code letter do?


(Boy, I sure opened a can of worms )

 

[grnzbra]

The alpha code could be used for any number of things. When I ran a database for a professional organization, we used the alpha to designate type of membership: Regular, Associate, Corportate, Student, Library, etc. New member numbers were assigned by stripping off looking for the highest number with a particular alpha, stripping off the alpha and adding 1 after converting to a numeric. Then converted back to new alpha and added leading zeros in a loop until it was the desired length.

That way, anyone seeing the number knew exactly what kind of member they were dealing with. (Saved a field in the member directory. Saving that field was a very big deal)

 

[gemma-the-husky]

surely you could do it the other way

store the membership number as along, or as an autonumber.

store the membership type as an attribute

concatenate then for display purposes

 

[KenHigg]

surely you could do it the other way

store the membership number as along, or as an autonumber.

store the membership type as an attribute

concatenate then for display purposes

This is the way I would do it. Do you really need to do all that parsing, etc. when the piece of data is obviously an attribute worthy of it's own field...

 

[Rich]

Very true, you did not say that it couldn't be done. But you did discourage the idea instead of answering the question.

However, uttamsaxena's question was not whether it was a good idea; uttamsaxena's question had to do with how it could be done, and that was the question I answered.

"Rule 1: The customer is always right. Rule 2: If the customer is wrong, see Rule 1."

So based on that theory we should also advise newbees on how to save calculated fields simply because they ask how?
Doesn't seem very responsible to me.

 

[ByteMyzer]

So based on that theory we should also advise newbees on how to save calculated fields simply because they ask how?
Doesn't seem very responsible to me.

Rich, third normal form is good for scenarios where the associated data does not change. However, there are some cases where it makes sense to replicate the data.


Invoice transactions are a prime example. Let's say you have a database with the tables:
tblPartMaster
PartNo | Description | Price

tblInvoice
InvoiceNo | InvoiceDate | PartNo | Price

Now you have an entry like this:

PartNo | Description | Price
--------------------------
100001 | Misc part 1 | 1.00

Then you post an invoice entry:
InvoiceNo | InvoiceDate | PartNo | Price
50000001 | 2006-10-16 | 100001 | 1.00

Tomorrow you update the price of said part to 1.50. Using third normal form, your invoice transaction from today would read:
InvoiceNo | InvoiceDate | PartNo | Price
50000001 | 2006-10-16 | 100001 | 1.50

Obviously, the Price field must be replicated.


My point is that we should not make assumptions about the user's needs based on insufficient information.

 

[The_Doc_Man]

I'm on the side that says tell the requester how to do it. If you will note in the very first post, uttamsaxena says it won't be the prime key because that is going to be an autonumber. This is a number that a person is going to know and it will have a fixed format. I can think of many reasons why I might wish to do this. I won't bore you with a list.

The point is that the question wasn't "How do I make a primary key for my table so that the key has structure X?" We've all seen those. This ain't that case. We SHOULD dissuade folks from THAT mistake and most of us know why it is a bad idea.

The question is, "How do I generate a unique identifier for which a defined structure exists?" Because it wasn't a "structured prime key" question, I inferred that it was related to some oddball requirement imposed from the outside. Therefore, pestering a new member who asked a simple question with enough info to show it wasn't prima facie an error is, IMHO, poor form.

As a matter of fact, my own current employer has me at a government site where we have a unique identifier consiting of first initial, last initial, and four digits. No, it isn't an employee number. It is a LOGIN identifier for a government computer with a fixed username requirement and, for security reasons, we don't choose our own login names.

Quoth KenHigg:

I didn't say that it couldn't be done. Just that, from my experience, building this type of logic into an id number rarely yields any sustainable benefit considering the over head.

Re-computing forumulas in a query every time rather than storing a computed value also involves CPU overhead. Yet there are reasons why we do it.

Generating an autonumber for a table involves some overhead in the CPU and in the TableDef. Yet there are reasons why we do it.

Therefore, Ken, I think your initial response was rather off-handed and betrayed less than your normally helpful attitude. Had a bad day?

 

[KenHigg]

...Therefore, Ken, I think your initial response was rather off-handed and betrayed less than your normally helpful attitude. Had a bad day?

Could be... It was just my 2cents. Sorry gang. Any who... seeing as how the original poster has never checked back in I'm guessing we're all spinning our wheels on this one -

 

[grnzbra]

surely you could do it the other way

store the membership number as along, or as an autonumber.

store the membership type as an attribute

concatenate then for display purposes

The member number wasn't the key field. By doing all the busy stuff as the number was entered, it eliminated having to loop through the process of adding leading zeros and concatinating the alpha every time the member number was needed. Also, autonumber wouldn't work because an Associate member could have the same numeric portion as a Regular member.