Update table to replace primary key

[Raymond Sanders]

My database tracks all of the staff development sessions provided for the
past ten years, thus we have thousands of records. Our school district
wants all departments to move away from using an employee's Social Security
number to a number assigned by the district. Each employee has been
assigned a unique six digit number.

In my staff development database, the employees data (Social Security
number, campus, job description, etc.) are in a table, "EmployeesTbl", with
the primary key being the Social Security Number. Classes data are in a
table, "ClassesTbl" with some of the fields being ClassNum (an autonumber),
class name, class description, etc., with the ClassNum being the primary key
in this table.

A third table, "AttendTbl", links the two and shows each class (staff
development session) each employee has taken. This table has a a foreign
key, "SocNum", that links it to the EmployeesTbl, and another foreign key,
"ClassNum", that links it to the ClassesTbl.

I have added the EmpNum field to the EmployeesTbl, and all of the the
district assigned employees' numbers have been entered. I have also added
this field to the AttendTbl. Is there a means of using a query to fill in
the employees number in this table. Is so, I'll then change the primary key
in the EmployeesTbl to be the employee number and the foreign key in the
AttendTbl to be the same. If not, we face the task of having to key in
18,000 records!

Thanks for any help,
R. Sanders

 

[John Spencer]

You should be able to use an update query to set the EmpNum in the
AttendTbl to the EmpNum in the EmployeesTbl

UPDATE AttendTbl INNER JOIN EmployeesTbl
ON AttendTbl.SocNum = EmployeesTbl.SocNum
SET AttendTbl.EmpNum = [EmployeesTbl].[EmpNum]

After you have updated the AttendTbl you can
-- drop the relationship between the two tables
-- change the primary key in the EmployeesTbl to EmpNum
-- add a relationship between the two tables on EmpNum

If everything works out then you can remove the SocNum field from the
attendTbl.

BACKUP your data FIRST. Just in case.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================