job history table

[mseo]

hi,
I have a table for job history that contains the history of the rotations between the departments for specific employee
then
I have form for Edit employees and I need when I open this form on specific employee and edit the department textbox to insert new record with new department and start date ( date of edit) and end date (today) as long as he still in the same department
I am using bound controls in my form
any suggestion would be appreciated
thank you very much

[MMcCarthy]

Mseo

You haven't given enough information to allow anyone to attempt to answer your question. Please provide the metadata of this table and if different the metadata of the table behind the bound controls on your form.
Here is an example of how to post table MetaData :
Table Name=tblBooking s

[i]Field; Type; IndexInfo[/i]
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

It is not at all clear what you are trying to do. How do you decide if an emploee is still in department? I really can't tell exactly what you are trying to do here so please provide more information.

Mary

[mseo]

hi,
thank you for your dedication

here is the structure for each table
1- tbl_employees

 employee_id ; autonumber ; pk
departmentid ; number ; fk
jobtitle ; string ; fk
hiredate; date/time
birthdate ; date/time
firstname ; string
middlename ; string
lastname ; string
Address ; Memo
City ; string
Phonenumber ; string
resignation ; yes/no
resignationdate ; date/time

2-tbl_job_history

employee_id ; number ; fk
departmentid ; number ;fk
job_title ; string ; fk
startdate ; date/time
enddate ; date/time

I have a form for edit employees
I need when I edit an employee if the job_title and departmentid change to insert new record into tbl_job_history
with the data changed
If you need more detail please let me know
It's now 16 days trying to solve this
thank you again and over again
MSEO

[mseo]

hi,
I really need any suggestion to do this,

[mseo]

ok,
I just need to know how to check if the department ID changed or not (compare the old department and new inserted on in the form)
in other words
how to save the old department ID in aviarable to compare the old department id and new department id
thank you very much
any help would be appreciated

[jimatqsi]

May I suggest you add a button to the employee edit form. If you click this button, a new form could be opened to add a record to the job history table. Your user can simply fill in the necessary data and click "Save" on that form (which could be a subform on a different tab of the main form)

When the Save button is clicked, the job history record could be saved and then the data entered on that form could be use to update the main form before the sub-form is automatically closed.

I realize this is a high level description of the suggested process, and you might need a lower level description, including some lines of code. Just let us know what additional help you need.

Jim

[FishVal]

Your problem actually arises from that fact that you don't store employee data in a proper way. The most recent record in tbl_job_history should be recognized as employee 's current assignment. I guess it is the same that Jim has tried to suggest you but expressed in another way.

This by the way means that you have have an extra field [enddate] in tbl_job_history as well.

Kind regards,
Fish