I have inherited a database.
There is one portion relating to payroll, hours, jobs, and time sheets that I need help with.
On each time sheet there are multiple employees. For each employee there will be multiple time sheets (even possibly for each day), Employees' time sheets will exist across multiple jobs per day. I have the table structure at this point. The trouble I am having is that for each employee there will be Standard Pay hours and Premium Pay hours. It is based on each week at 40 hours. Everything over 40 hours per employee in a week is premium pay. When we bill the clients we also have a standard and premium billing rate.
1. The problem is that the time sheets will not necessarily be entered in order.
2. To further complicate things the billing hours do not always match the payroll hours. Let me explain: The billing hours for some clients are based on an 8 hour standard pay. So anything over 8 hours in a day per employee is premium billing and everything less is standard. Therefore we run into an employee only working 32 hours in a week, but if he worked 2 days for one of these special clients at 16 hours per day. He would be paid 32 hours standard pay, but we would bill at 16 hours standard and 16 hours premium.
Does this make since?
If so read on, if not please ask and I will clarify...
With these two challenges, this is what I determined to do. Instead entering hours as standard and premium, I simply want to enter the total hours. So an entry may look something like this:
Time sheet(one side):
Time sheet # / Job # / Emp # / TSDate
100001-------B0122---0520----9/5/07
The details(many side):
starttime / endtime / lunch / hours
05:00------07:30---00:30----2
09:00------12:00---00:00----3
This will take place across multiple jobs per day. So what I need to do is calculate the standard and premium hours both for billing and payroll. I need to do it on each line for the details table because I need to match it exactly to the job. So in the example above if employee "0520" worked 10 hours on 9/25/07, but only 5 on job B0122. He also has worked 38 hours for the current week before this work. I need the system to recognize when 2 hours are entered for 05:00-07:30 whether the hours should be premium or standard pay and billing.
The details(many side):
starttime / endtime / lunch / hours / stbillhours / prbillhours / stpayrollhours / prpayrollhours
05:00------07:30---00:30----2--------2----------0-------------2---------------0------
09:00------12:00---00:00----3--------1----------2-------------0---------------3------
Make since? How do I do it?
I assume with lookups, but I need to check to see if a previous start and end time has been entered. Can this be accomplished?
Should I being doing this another way? Keep in mind that the database is much more intricate. I've tried to simplify it in this post.
Eric
There is one portion relating to payroll, hours, jobs, and time sheets that I need help with.
On each time sheet there are multiple employees. For each employee there will be multiple time sheets (even possibly for each day), Employees' time sheets will exist across multiple jobs per day. I have the table structure at this point. The trouble I am having is that for each employee there will be Standard Pay hours and Premium Pay hours. It is based on each week at 40 hours. Everything over 40 hours per employee in a week is premium pay. When we bill the clients we also have a standard and premium billing rate.
1. The problem is that the time sheets will not necessarily be entered in order.
2. To further complicate things the billing hours do not always match the payroll hours. Let me explain: The billing hours for some clients are based on an 8 hour standard pay. So anything over 8 hours in a day per employee is premium billing and everything less is standard. Therefore we run into an employee only working 32 hours in a week, but if he worked 2 days for one of these special clients at 16 hours per day. He would be paid 32 hours standard pay, but we would bill at 16 hours standard and 16 hours premium.
Does this make since?
If so read on, if not please ask and I will clarify...
With these two challenges, this is what I determined to do. Instead entering hours as standard and premium, I simply want to enter the total hours. So an entry may look something like this:
Time sheet(one side):
Time sheet # / Job # / Emp # / TSDate
100001-------B0122---0520----9/5/07
The details(many side):
starttime / endtime / lunch / hours
05:00------07:30---00:30----2
09:00------12:00---00:00----3
This will take place across multiple jobs per day. So what I need to do is calculate the standard and premium hours both for billing and payroll. I need to do it on each line for the details table because I need to match it exactly to the job. So in the example above if employee "0520" worked 10 hours on 9/25/07, but only 5 on job B0122. He also has worked 38 hours for the current week before this work. I need the system to recognize when 2 hours are entered for 05:00-07:30 whether the hours should be premium or standard pay and billing.
The details(many side):
starttime / endtime / lunch / hours / stbillhours / prbillhours / stpayrollhours / prpayrollhours
05:00------07:30---00:30----2--------2----------0-------------2---------------0------
09:00------12:00---00:00----3--------1----------2-------------0---------------3------
Make since? How do I do it?
I assume with lookups, but I need to check to see if a previous start and end time has been entered. Can this be accomplished?
Should I being doing this another way? Keep in mind that the database is much more intricate. I've tried to simplify it in this post.
Eric
Last edited:
