Combine 2 IDs Into One 1

[Happy2]

Hello,

I am look for help.

I have 2 tables: tblState and tblManager. The tblState table has 2 varchar fields as followed:
State LocationID
===== ==========
AR 012
CA 015

The tblManager table has the following fields:
ManagerID LName FName State HiredDate
1 Smith Mary CA 1/1/00
2 Lee Henry CA 1/10/02
3 Lee Nancy AR 10/14/01

I now have to add a new column called NewMgrID to the tblManager table. The data for this field = LocationID + 01 (AutoNumber with 0 in the front). This autoNumber Starts with 01 for every different state. For Example, the data in the field for Mary Smith is 01501, for Lee Henry is 01502, and for Lee Nancy is 01201. My tblManager table now is:

ManagerID LName FName State HiredDate NewMgrID
1 Smith Mary CA 1/1/00 01501
2 Lee Henry CA 1/10/02 01502
3 Lee Nancy AR 10/14/01 01201

I have to have this module soon while I am having a hard time to get it on time. I am a beginner. I appreciate for your help.

Thanks,

 

[jrbarnett]

You don't specify which app this is written in, but I will assume access as you mention tables. Try the following from the immediate windows:

DoCmd.RunSQL "Update tblManager inner Join tblState on tblmanager.state = tblstate.state set Newmgrid = CStr (tblstate.locationid) & Format (ManagerID, ""00"&quot"

John

 

[Happy2]

Hi John,

I almost got it. I need to set the NewMgrID = tblState.LocationID + Autonumber (Not MangerID). The Autonumber should be always 2 digits. e.g. 01, 02,...10, 11 let say for State CA and repeats for state AR as 01 again. The order of this autonumber must be base on HiredDate of each Manager. Can you help me out please.

Thanks,