Problem with Wage Rate updating

[gscma1]

Hi, I have a wages database which is used weekly to determine the weekly wage of each employee based on the hours they've worked.

each employee has a daily wage rate which may increase over time.

I have a Master Wage Sheet which calculates the employees gross, tax and net wages. The gross total is achieved by the calculation : [Hours]*[Wage Rate]/8

Each month each worker has to have a voucher completed with his earnings from that month. currently this works fine, however if an employee has a wage increase part way through a month then this report will bring up the employees wages as per the new wage rate for all weeks in that month.

Does anyone know of an effective way to solve this problem?

 

[SgtJarrow]

This is one of those areas where you may have to break normal database rules and store the wage rate each week, even if it is the same for weeks at a time. You need to determine a way to hold the wage rate for a period of time, such as weekly if it can only change at the start of a week. You may need to store the actual calculated value, or maybe you can just store the employeeid, weekstartdate, and wagerate and reference those when doing the calculations. If you need some more specific help, please provide us some sample data so we can determine your structure and how to best help you adapt it.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[missinglinq]

Did you have no luck with lespaul's rather extensive help when you posted the problem back in July?

The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[missinglinq]

One simple approach would be to have each employee have two fields for wage rates and two fields for hours worked, say WageRate1 & WageRate2 and HoursWorked1 & HoursWorked2. Enter the hours under the old wage in WageRate1 & HoursWorked1 and the hours under the new rate in WageRate2 & HoursWorked2.

The advantage of this is that the rate change can happen anywhere in the pay cycle, not just at the beginning of a week. After the transition pay cycle has been processed and your vouchers printed, go back and enter the new wage rate in the field WageRate1, and you're ready for the new cycle.

The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[barny2006]

one way would be to have a table for wage rate with effective date of the wage. each employee will have an effective wage date. and each day's work is calculated by the date that it applies to. the table will have emp_id, wage_rate, and effective date. this will also show a history of increases for each employee. each day's hours is multiplied by the rate that it applies to, and calculated.

 

[lespaul]

Barny, I think that's the essence of the suggestion I tried months ago.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi