Update column value??

Hi...

First I'll like to thanks everyone that took their time to try and help me out so far..especially to shafique..thank you all.

Okie..now back to my question..I've got this problem which i m not sure whether can be done or not.

|-Emp_id-|Full_name|Join_date|Plant_id|Plant_name|-Status|
|--001---|--ABCD--|1/1/2004|--A001-|-----A-----|Transfer|
|--002---|--BCDE--|1/2/2004|--A002-|-----A-----|-NULL--|
|--003---|--ABCD--|1/4/2004|--B001-|-----B-----|-NULL--|
This table keep information bout employee transfer.

For example, employee name ABCD start joining the company at 1/1/2004 at plant with Plant_id A001 and was given an Emp_id 001. Then he was transfer to plant B001 and then given another different emp_id 003...if status id Transfer means that the employee have been transfer to another plant.

|-Emp_id-|Worktime|Workdate|-OT-|Plant_name|
|--001---|----8----|1/1/2004|--1-|-----A-----|
|--001---|----8----|2/1/2004|--0-|-----A-----|
.
.
|--001---|----8----|31/3/2004|-0-|-----A-----|
|--002---|----8----|1/2/2004|--2-|-----A-----|
.
.
|--003---|----8----|1/4/2004|--1-|-----B-----|
.
.
This 2nd table keep information bout employee working time.

I need to do some sort of a summary for an employee overall Worktime and OT records in the company, so need to have a common Emp_id to be able to extract those info bout the employee.

Thats y i need to be able to do something like this....
1) Find from the first table to see whether the person have a transfer or not
2) If got transfer, find with the same full_name and from status if got TRANSFER means is the old emp_id and the latest Emp_id for that employee is the one where status is NULL (eg: 001 is the old emp_id cos status is transfer which means 003 should be the latest emp_id)...then in the 2nd table, i need to rewrite/update the latest Emp_id and Plant_name in the 2nd table

To something like this
|-Emp_id-|Worktime|Workdate|-OT-|Plant_name|
|--003---|----8----|1/1/2004|--1-|-----B-----|
|--003---|----8----|2/1/2004|--0-|-----B-----|
.
.
|--003---|----8----|31/3/2004|-0-|-----B-----|
|--002---|----8----|1/2/2004|--2-|-----A-----|
.
.
|--003---|----8----|1/4/2004|--1-|-----B-----|
.
.

Hope this question wont sound weird and funny

Thanks in advance.


regards
YuLing