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

 

[wongm003]

If you create the column in the table then use an update query to add the codes...

1. Update to: Left([Table Name]![first name],1) & Format([Table Name]![sr no],"0000")

OR

2. Update to: Format([Table Name]![date joined],"yyyy") & Format([Table Name]![serial number],"000")

I'm assuming you are keeping the serial number in another field. Not quite sure what the requirements/criteria for picking between the two ways to generate the code... why don't you want to use the primary key as the code or some part of the code?