How to generate employee code

uttamsaxena

Board Regular
Joined
Apr 22, 2003
Messages
179
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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

wongm003

Board Regular
Joined
Aug 8, 2005
Messages
237
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?
 

Forum statistics

Threads
1,141,757
Messages
5,708,346
Members
421,566
Latest member
7Nabisco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top