how to update employees' details without removing the old details?
- Thread starter Harrold
- Start date
Nothing, in fact to accomplish this you should remove something from this table--the Designation field.
To accomplish what you want you would create a new table called something like 'Designations' which would be comprised of 3 fields: Designation (the same field as in the table you listed), Autonumber (which is a foreign key to the table you listed) and DesignationDate which holds the date that designation began.
With this you can now keep track of everyone's designations no matter how many designations they have had.
2 other notes: 1. Autonumber is a horrible name for a field--its an access reserved word which could cause conflicts down the line. EmployeeID is a better name for this field. 2. You have listed two fields named 'TerminationDate' which can't be the case, but more importatnly seem redundant, you probably only need the actual date field not the checkbox.
To accomplish what you want you would create a new table called something like 'Designations' which would be comprised of 3 fields: Designation (the same field as in the table you listed), Autonumber (which is a foreign key to the table you listed) and DesignationDate which holds the date that designation began.
With this you can now keep track of everyone's designations no matter how many designations they have had.
2 other notes: 1. Autonumber is a horrible name for a field--its an access reserved word which could cause conflicts down the line. EmployeeID is a better name for this field. 2. You have listed two fields named 'TerminationDate' which can't be the case, but more importatnly seem redundant, you probably only need the actual date field not the checkbox.
Thanks for your explanation, Plog.
After given it a thought, i am confused again. Say employee A was a clerk in November 2011 and been promoted to officer in December 2011.
Hence, in my query/report, i shall populate the data by selecting
1) employee name in tblEmployee
2) designation in tblDesignation
3) desingationDate in tblDesignation?
When there are many employees with different designations, am i supposed to assign designationDate to each designations?
Thanks
After given it a thought, i am confused again. Say employee A was a clerk in November 2011 and been promoted to officer in December 2011.
Hence, in my query/report, i shall populate the data by selecting
1) employee name in tblEmployee
2) designation in tblDesignation
3) desingationDate in tblDesignation?
When there are many employees with different designations, am i supposed to assign designationDate to each designations?
Thanks
I'm not clear on what you want to appear on your query/report in your example. Do you want all designations or just the most current for each employee? Could you provide sample data in the table and then how you would like that to come through onto your query/report?
Privateer
Registered User.
- Local time
- Today, 12:00
- Joined
- Aug 16, 2011
- Messages
- 193
Harrold,
You asked, "am i supposed to assign designationDate to each designations" and the answer is yes, you are basically recording a transaction, or promotion, so enter the date it happened. Later, when you need the most recent designation you can sort the query in decending order by date and grab the first record.
But, because this is a one to many relationship, you can also create a form with the employee data and a subform with a history of that person's designations and the date they took place.
You asked, "am i supposed to assign designationDate to each designations" and the answer is yes, you are basically recording a transaction, or promotion, so enter the date it happened. Later, when you need the most recent designation you can sort the query in decending order by date and grab the first record.
But, because this is a one to many relationship, you can also create a form with the employee data and a subform with a history of that person's designations and the date they took place.
Similar threads
