Job Costing Database with Changing Rates
Hello,
I have created an access database for one of my customers which is used for timesheet entry.
Within the database i have a table called "Timesheets" which contains all of the timesheet entries for all of the employees. Each entry is assigned against a job (I have another table which contains all of the jobs which are available).
In this case all of my employees bill out at the exact same rate no matter what job they work on. However on weekends they bill at a different rate. Saturdays the first two hours the employee works is billed at 1.5x the normal rate and any further work is billed out at 2x rates. All Sunday is billed out at the same rate of 2x.
What i am trying to accomplish is a way for access to automatically find the first two hours that an employee works on a Saturday so that i can bill it against the job at the lower rate and then will all remaining work at 2x.
One of the problems that i see is that i might not have a job or two jobs that will always line up on 2 hours. For example if Joe worked 9-12 on a Saturday, the first 2 hours of that 3 hour job is billed at 1.5x and the final hour is at 2x.
My second issue is that an employee may work on 5 jobs in a single day or come back and working on a single job multiple times with breaks in between. I need a way to check and see if an employee has already billed out his 2 hours total of 1.5x and if not use the remaining time up before billing at 2x.
At the end of all of this i need to have reports which can show where the 1.5x have been allocated and where the 2x has been allocated.
Any thoughts would be great as im all out of ideas.
I have created an access database for one of my customers which is used for timesheet entry.
Within the database i have a table called "Timesheets" which contains all of the timesheet entries for all of the employees. Each entry is assigned against a job (I have another table which contains all of the jobs which are available).
In this case all of my employees bill out at the exact same rate no matter what job they work on. However on weekends they bill at a different rate. Saturdays the first two hours the employee works is billed at 1.5x the normal rate and any further work is billed out at 2x rates. All Sunday is billed out at the same rate of 2x.
What i am trying to accomplish is a way for access to automatically find the first two hours that an employee works on a Saturday so that i can bill it against the job at the lower rate and then will all remaining work at 2x.
One of the problems that i see is that i might not have a job or two jobs that will always line up on 2 hours. For example if Joe worked 9-12 on a Saturday, the first 2 hours of that 3 hour job is billed at 1.5x and the final hour is at 2x.
My second issue is that an employee may work on 5 jobs in a single day or come back and working on a single job multiple times with breaks in between. I need a way to check and see if an employee has already billed out his 2 hours total of 1.5x and if not use the remaining time up before billing at 2x.
At the end of all of this i need to have reports which can show where the 1.5x have been allocated and where the 2x has been allocated.
Any thoughts would be great as im all out of ideas.
Simon
It would be useful if you could post the table structure (fieldname and datatypes) for your timesheet table.
ASKER
Timesheet Table:
ID - AutoNumber
Job Number - Number (Used to match Job Name in a table called Jobs)
Date - Date/Time (Date worked, in format d/mm/yyyy)
Employee Name - Number (Used to match Employee Name to a table called Employees)
Time Start - Date/Time (Start time in format Short Time. All in 24hr time)
Time Finish- Date/Time (Finish time in format Short Time. All in 24hr time)
ID - AutoNumber
Job Number - Number (Used to match Job Name in a table called Jobs)
Date - Date/Time (Date worked, in format d/mm/yyyy)
Employee Name - Number (Used to match Employee Name to a table called Employees)
Time Start - Date/Time (Start time in format Short Time. All in 24hr time)
Time Finish- Date/Time (Finish time in format Short Time. All in 24hr time)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.