Employment end date (1 Viewer)

Siegfried

Registered User.
Local time
Today, 13:19
Joined
Sep 11, 2014
Messages
105
Dear experts,

In all the examples I came across on the internet so far for an Employees Table, I notice that only a hire date and, at times, the date of birth are mentioned?
Is it incorrect to add an employee end date in that table?

EmployeeT
EmployeeID_PK
EmplFirstName
EmplLastName
EmplFunctionID_FK
EmplDepartmentID_FK
EmplDOB
EmplStatusID_FK
EmplHireDate
EmplEndDate ??
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:19
Joined
Aug 30, 2003
Messages
36,118
Sure, I've got a commercial HR app that has both. It has a rehire date too. In an app I wrote, I had a related table with hire/term dates, because we have a fair number of employees that leave and come back, so I had a separate record for each instance.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:19
Joined
Feb 19, 2002
Messages
42,970
Paul covered this but, most of the time people are hired and leave once each. However, since in the real world, a person could be hired more than once and therefore leave more than once, a proper representation would require a separate table.

When you include termination date, you should also include termination reason for completeness.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:19
Joined
Feb 19, 2013
Messages
16,553
assuming the person is hired as an employee they will normally have a payroll number. That number is unique to that particular employment. If they leave and then return, they will have a different payroll number. Reason - for the purposes of calculating tax, providing P45 or equivalent, etc the values can only relate to current employment, not previous employment. So they can still go in the same table if the table includes payroll number (indexed, no dups). You might have a field to link back to the previous employment for completeness.

Agree you would need a termination date in the employee record (plus reason as Pat suggests), but you would almost certainly have a child table regarding role - this would not need a termination/end date because if they change roles the end date would be the day before the start date of the next role. For that reason I would move these fields to a role table (on the basis they can change during employement)- unless you take on employees who do not have a role?

EmplFunctionID_FK
EmplDepartmentID_FK
EmplStatusID_FK
EmplHireDate (rename as EmpFromDate or similar)
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:19
Joined
Aug 30, 2003
Messages
36,118
If they leave and then return, they will have a different payroll number.

Perhaps this varies by country. In the US, they can come back and have the same employee number. If they worked 3 months at the start of the year, terminated and then got rehired for the last 3 months of the year, the 6 months of earnings would be combined for reporting purposes.
 

Dreamweaver

Well-known member
Local time
Today, 12:19
Joined
Nov 28, 2005
Messages
2,466
My company has on a number of occations re-employed employees so you really need to allow for that.
thay all have a different clock number as they are reused but are tracked using there N.I. Number I think.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:19
Joined
Feb 19, 2013
Messages
16,553
Perhaps this varies by country.
sounds like they do - the requirement as I outlined is based on EU requirements
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:19
Joined
Jan 23, 2006
Messages
15,361
Siegfried,
In your organization is there any history of employee employment that can help with your decision?
 

HiTechCoach

Well-known member
Local time
Today, 07:19
Joined
Mar 6, 2006
Messages
4,357
EmployeeT
EmployeeID_PK
EmplFirstName
EmplLastName
EmplFunctionID_FK
EmplDepartmentID_FK
EmplDOB
EmplStatusID_FK
EmplHireDate
EmplEndDate ??


A person can have multiple employments. During each employment, they can work in multiple departments.

The way I normalize the data is with 3 tables to maintain a complete history.

1) People - Name, DOB, etc.

2) People_Employment (many side to 1 People) - Start Date, End Date, Status, etc.

3) People_Employement_Departments (Many side to 1 People_Employmen) Dept ID, Start Date, end date
 

Siegfried

Registered User.
Local time
Today, 13:19
Joined
Sep 11, 2014
Messages
105
Hi everyone,

thanks for the many response.
I designed my table like this:

1584354013303.png

1584354032811.png

1584354122681.png

1584354163881.png

being: H hired, T temp, S student, C contracted for a period of time but not full time.

I don't need any wages info in this database, I need the employee info as IT to be able to assign assets to users and create lists like: office telephone list, employees details including telephone and email for name cards, login details, list of computers/monitors assigned to users.
If an employee leaves I will mark his status as NLE (no longer employed) but won't delete him/her from the database.

If you have any remarks/observations, please let me know, thanks.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:19
Joined
Feb 19, 2013
Messages
16,553
don't think you need the employeeDateT or DataTypeT tables, just have joindate and leavedate in the employeeT table. Not sure why you need DOB for an IT function unless used for some sort of verification purpose? Same goes for Gender

You may want to put employee notes into a separate table if it is likely there can be multiple notes per employee.

Not sure you need the joining/leaving/NLE status values - they can easily be calculated e.g.

joining - if today<joindate
leaving - if today <leavedate (or perhaps leavedate-today<30 if you only want the status to change 30 days before leaving)
NLE - if today>leavedate

then from that you can have a simple yes/no tickbox for active/absent so you then do not need the EmployeeStatusT table.

However you might want an absent type (vacation/long term sick/maternity, etc) in which case you probably need another table for absent types and another many to many linking table between employee and type to include start/finish dates for the absence.

agree do not delete employee from database
 

Siegfried

Registered User.
Local time
Today, 13:19
Joined
Sep 11, 2014
Messages
105
don't think you need the employeeDateT or DataTypeT tables, just have joindate and leavedate in the employeeT table. Not sure why you need DOB for an IT function unless used for some sort of verification purpose? Same goes for Gender

You may want to put employee notes into a separate table if it is likely there can be multiple notes per employee.

Not sure you need the joining/leaving/NLE status values - they can easily be calculated e.g.

joining - if today<joindate
leaving - if today <leavedate (or perhaps leavedate-today<30 if you only want the status to change 30 days before leaving)
NLE - if today>leavedate

then from that you can have a simple yes/no tickbox for active/absent so you then do not need the EmployeeStatusT table.

However you might want an absent type (vacation/long term sick/maternity, etc) in which case you probably need another table for absent types and another many to many linking table between employee and type to include start/finish dates for the absence.

agree do not delete employee from database
Hi CJ,

Thanks for the tips! (y)
The DOB I'm using to set up a code, we have this desktop application which connect to the land line telephone. At setup you have to change the default code to a new individual user 6-digit-code, I've opted to use the data of birth for that. So now and then the application prompts to enter the code again and then the user knows it's his DOB. If I have to start keeping tracks of codes I generate myself then I will have to keep informing the users as they tend to forget, if they go on leave for 3 weeks for example I usually have to reset their login password as they forgot it....
I will take your advise on the Dates. Never thought of doing it like that.
I indeed also do want the absent info but was still trying to figure that out as I had the active, absent, joing, leaving, NLE already, was struggling with that setup. Just trying to figure out how to handle that data then. Let's say an employee is on long term sick leave, absent - long term sick leave from Feb 1st 2020 till May 15th 2020 and returns on the 16th of May, do I simply delete that absent status record then?
How would you display that info in an overview? If you list the active users how could you see in that same list both preset and absent due to... users?

best regards,

Siegfried.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:19
Joined
Feb 19, 2013
Messages
16,553
do I simply delete that absent status record then?
No - your tables would look something like

tblEmployees
EmployeePK
EmployeeName
...
...

tblAbsentReasons
ReasonPK
ReasnDesc

tblEmployeeAbsences
EmpAbsPK
EmployeeFK
ReasonFK
FromDate
ToDate

How would you display that info in an overview? If you list the active users how could you see in that same list both preset and absent due to... users?
use a query with a left join between EmployeePK and EmployeeFK and ReasonFK and ReasonPK. That will list all employees and if they were absent the reason. Use a date criteria to limit the period you are looking at
 

Siegfried

Registered User.
Local time
Today, 13:19
Joined
Sep 11, 2014
Messages
105
No - your tables would look something like

tblEmployees
EmployeePK
EmployeeName
...
...

tblAbsentReasons
ReasonPK
ReasnDesc

tblEmployeeAbsences
EmpAbsPK
EmployeeFK
ReasonFK
FromDate
ToDate

use a query with a left join between EmployeePK and EmployeeFK and ReasonFK and ReasonPK. That will list all employees and if they were absent the reason. Use a date criteria to limit the period you are looking at

Dear CJ,

Thanks for your help, I have implemented your suggestions to my database. Still have a question though.
How would you go about with updating the employee's absence?
In below example of the Employee Overview: if Peter returns to the office, do I put a button behind each record in EmployeeOverviewF to clear the absence info, would that be an idea?
1584702090512.png

1584702300752.png


I'm also trying to figure out if I can set the checkbox value basis absence. Meaning if an employee is absent then the checkbox is True, else False.
I've uploaded my test database for the Employee part.

Best regards,

Siegfried
 

Attachments

  • 1584702058080.png
    1584702058080.png
    29.5 KB · Views: 96
  • TEST_Employees.zip
    33.3 KB · Views: 103

CJ_London

Super Moderator
Staff member
Local time
Today, 12:19
Joined
Feb 19, 2013
Messages
16,553
not quite sure what you mean - I think you means if the todate is completed and is earlier than today then the active box should be ticked?

you appear to have an 'isActive' field in the employees table - should not be required as it can be calculated as and when required - but you are calling it absent on your form.

You will be building a history of absence - sick one week, holiday the next and maternity the week after :)

so for today you need to see a) if there is a record where the from date is before today AND the todate is null (which means they have not returned) OR has a enddate greater than today (they are expected to return later). If there is then they are absent, otherwise they are active.

Your form as designed at the moment will return multiple records if an employee has more than one absence - but looks like you are expecting only a single record. So suggest do the following.

1. Create this query and call it EmployeeCurrentAbsenceQ

SQL:
SELECT *
FROM EmployeeAbsenceT INNER JOIN AbsentReasonT ON EmployeeAbsenceT.ReasonFK=AbsentReasonT.ReasonID
WHERE FromDate<=Date() AND nz(ToDate,Date())>=Date()

2. In your EmployeeOverviewQ2 query, replace the sql with this

SQL:
SELECT EmployeeT.EmployeeID, EmployeeT.FirstName, EmployeeT.LastName, DepartmentT.DepertmentAbbrev, EmployeeT.IsActive, [EmplAbsID] Is Not Null AS Absent, EmployeeT.JoinDate, EmployeeT.LeaveDate, IIf(Date()<[Joindate],"joining",IIf(Date()>[LeaveDate],"NLE",IIf([LeaveDate]-Date()<30,"leaving","active"))) AS Status, EmployeeCurrentAbsenceQ.ReasonDesc, EmployeeCurrentAbsenceQ.FromDate, EmployeeCurrentAbsenceQ.ToDate
FROM (DepartmentT INNER JOIN (ContractT INNER JOIN EmployeeT ON ContractT.ContractID = EmployeeT.ContractFK) ON DepartmentT.DepartmentID = EmployeeT.DepartmentFK) LEFT JOIN EmployeeCurrentAbsenceQ ON EmployeeT.EmployeeID = EmployeeCurrentAbsenceQ.EmployeeFK
ORDER BY EmployeeT.FirstName

3. Modify your form to use the new Absent column (either as a new control or to replace your active control)

This shows the current view as of today. If you wanted a view for tomorrow, you'll need to modify the first query to use something other than date
 

Users who are viewing this thread

Top Bottom