Employee Table (1 Viewer)

HealthyB1

Registered User.
Local time
Today, 23:26
Joined
Jul 21, 2013
Messages
96
Hi There,
Many years ago I developed and I am still running a basic payroll system, At the present moment I have all the standard employee data fields and the system has been running well for many years. However I have a question, currently each year we hire several new hires and over the years we provide salary increases to each of the employees but they have different hourly rates for each employee for each year. At the present moment I have separate records for each year for that employee eg, John Smith(yr1), John Smith (yr2) ..etc with their weekly wage.
Is there a smarter way of tying the weekly wage to some external table so that I can better track things like accrued annual leave, long service leave etc for a single employee?
 

Ranman256

Well-known member
Local time
Today, 08:56
Joined
Apr 9, 2015
Messages
4,339
Wage should be its own table as a history, (tWageHist table)
empID, dateStart, Wage

the CURRENT wage can be on the tEmployee table.
there should be only ONE record of that John Smith in the tEmployee table.
 

Solo712

Registered User.
Local time
Today, 08:56
Joined
Oct 19, 2012
Messages
828
Hi There,
Many years ago I developed and I am still running a basic payroll system, At the present moment I have all the standard employee data fields and the system has been running well for many years. However I have a question, currently each year we hire several new hires and over the years we provide salary increases to each of the employees but they have different hourly rates for each employee for each year. At the present moment I have separate records for each year for that employee eg, John Smith(yr1), John Smith (yr2) ..etc with their weekly wage.
Is there a smarter way of tying the weekly wage to some external table so that I can better track things like accrued annual leave, long service leave etc for a single employee?

Hi,
basically you are looking at two different external tables. One is employee wage history, as Ranman suggests, and the other is Employee Attendance/Absence which tracks vacations, leaves, sick days, etc. Both tables would have the employee ID as the foreign key. Trying to figure out why employee was absent by looking at his/her paychecks is not the best way.

Best,
Jiri
 

HealthyB1

Registered User.
Local time
Today, 23:26
Joined
Jul 21, 2013
Messages
96
Wage should be its own table as a history, (tWageHist table)
empID, dateStart, Wage

the CURRENT wage can be on the tEmployee table.
there should be only ONE record of that John Smith in the tEmployee table.


A clarifying question if you will.
Assuming that the employee has an increase this week (21/Jan/19) and her wage goes from say $20 to $25/hour then her CURRENT wage rate would be set to $25/hour. If however I go back two months and edit or add a timesheet for that employee her wage would need to be at the $20/hour rate not the CURRENT wage of $25/hour. I am assuming that if I tried to do this then I would need to look at the date of the back dated timesheet and do a look up or the like against the history file to see what is the appropriate rate at that time. Am I on the right track?
Thanks in advance
 

Solo712

Registered User.
Local time
Today, 08:56
Joined
Oct 19, 2012
Messages
828
A clarifying question if you will.
Assuming that the employee has an increase this week (21/Jan/19) and her wage goes from say $20 to $25/hour then her CURRENT wage rate would be set to $25/hour. If however I go back two months and edit or add a timesheet for that employee her wage would need to be at the $20/hour rate not the CURRENT wage of $25/hour. I am assuming that if I tried to do this then I would need to look at the date of the back dated timesheet and do a look up or the like against the history file to see what is the appropriate rate at that time. Am I on the right track?
Thanks in advance

Yes, that's the idea !

Best,
Jiri
 

HealthyB1

Registered User.
Local time
Today, 23:26
Joined
Jul 21, 2013
Messages
96
Thanks Jiri,
I achieved the result I wanted using one query and then another based on the results of the first query being True or False. Messy but it works correctly.
I might have a look at doing it using a DLOOKUP to see if I can select correct wage rate using weekly timesheet date and comparing it to the History file dates for the correct salary at that the timesheet entry date to see if I can make it simpler.
Cheers
 

Users who are viewing this thread

Top Bottom