Table Reconciliation Question

[LLB]

I have been asked to make some modifications to a couple of DB's we use. One is a DB to track projects with hours (estimates and expended) and the other is used by a different department to track DCF's (or sub projects). Every project could have many DCF's.

With as little disruption as possible I've been asked to make the DCF db track man-hours and track work load by employee.

I got the DCF db to track hours but since in most cases more than one employee can be working on the DCF's I've been having problems making the hours specific to the employee since the db was designed to track the DCF.

My solution was to create a 3rd db just for Man-hours including DCF# and project# so that the hours could be spit out the other db's as needed and also track just what everyone is working on.

Here's my problem (though I'm sure it's not my only one). Each of these db's has a table for employees which have all the same name in them. I want to the employee table in the 3rd db but the EmpID which auto numbered in the other db's is different for each table. Is there a way to make the numbers coincide without losing the integrity of the data currently in those db's.

Sorry for the lengthy explanation but I'm new at this. I did do a search but couldn't come up with anything. I'd appreciate any suggestions on my situation.

Thanks

 

[namliam]

Only use 1 employee table, link the other 2 ....

Right click your table tab, find 'Linked tables' now link the employee table.

Presto.... That is the only way to go!!!

Note/edit: if you have been using the different tables you will need to update your related tables first, so dont delete the 'original' table right away....

Regards

 

[LLB]

Note/edit: if you have been using the different tables you will need to update your related tables first, so dont delete the 'original' table right away....

I had planned on linking the tables. There is existing data in both db's that reference the seperate employee tables with different ID's. If I create a new employee table in the new db how do I maintain the existing employee information in the seperate db's if I change the ID #'s? Is there a way to update the existing records before liking the new db so that the ID #'s are the same? If so, how?

Thanks

 

[namliam]

You will have to run an update query on the FK. or if you have referential update integerity set you may get by on taking the Employee ID to number instead of autonumber and Updateing (update query also) that field. Trick is to find the proper link between employees.

During this proces the DB should NOT be accessable to users.....


Regards