1. How come an employee can have multiple statuses? EID=32 has 2 statuses, both of which have an Estatus of ACTIVE. So does he really have 2 current statuses or does the one with the latest EffDate take precedence and ESID=21 become an older one?
I am using the Employee Status table to keep track of PayRates, in case they switch departments, their position have changed or they are no longer employed by the company. Since these things can change I am using the EffDate to track it. The most recent will take precedence in calculating salary. The Main employee table will have the fields like DOB, NIS(SSN) etc. things that will never change.
2. Should TimeSheet be connected to EmpInfo or EmpStatus? I mean, rates in one table and hours are in another, seems like those should be directly related.
Yes, this was my error. TimeSheet should have been connected to EmpInfo but I wanted to bring in the current rate based on the last WDate in the totals query. (I was just experimenting)
3. In TimeSheet whats WDate? I mean you have TimeIN and TimeOut wich are datetimes, what does WDate tell you that could be different?
WDate is the Date the employee worked. TimeIn and TimeOut is a funny thing. How it works in the company is that a supervisor verifies the Employee's TimeIn and TimeOut and then passes it to me. Yes I can run a calculation on the TimeIn and TimeOut data but it may have discrepancies the Supervisor is aware of but not me.
4. Are all the hours calculated off TimeIn and TimeOut? Theres no data in TimeIn and TimeOut so I can't verify. But if all those hour fields are simple calculations you shouldn't store them.
So for Weekly paid employees I just use the times that are approved by the supervisor and enter them in the hour field(s) to calculate their pay which is paid hourly. For Monthly paid employees we just want their TimeIn and TimeOut to see their attendance and punctuality. The field is more for reference rather than calculations.