Solved

Job Costing Database with Changing Rates

Posted on 2014-12-17
5
11 Views
Last Modified: 2016-08-22
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.
0
Comment
Question by:infinnet
  • 2
5 Comments
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40504319
It would be useful if you could post the table structure (fieldname and datatypes) for your timesheet table.
0
 

Author Comment

by:infinnet
ID: 40504337
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)
0
 
LVL 18

Accepted Solution

by:
SimonAdept earned 500 total points (awarded by participants)
ID: 40504469
Hi, please try this. I changed your field names slightly, partly because 'DATE' is a reserved word and I wasn't sure if you'd posted exact field names. I can modify it if necessary, but you should get the idea.
The query includes a subquery that sums total time per employee per day per job and then uses the format 'ddd' function to get the day name and use that to identify 'Sat' and 'Sun' and then uses a conditional to calculate the billable hours.

SELECT qryHoursWorked.JobNo, qryHoursWorked.EmployeeName, qryHoursWorked.DateWorked, qryHoursWorked.Hours, Format([dateworked],"ddd") AS Dayname, IIf([dayname]="Sun",[hours]*2,0)+IIf([dayname]="Sat",[hours]*1.5,0)+IIf([dayname]="Sat" And [hours]*24>2,([hours]-2/24)*0.5,0)+IIf(Left([dayname],1)<>"S",[hours],0) AS BillableHours
FROM (SELECT tblTimesheet.JobNo, tblTimesheet.EmployeeName, tblTimesheet.DateWorked, Sum([timeend]-[timestart]) AS Hours
FROM tblTimesheet
GROUP BY tblTimesheet.JobNo, tblTimesheet.EmployeeName, tblTimesheet.DateWorked
ORDER BY tblTimesheet.DateWorked) as qryHoursWorked
;

Open in new window


Using this timesheet data:
ID      JobNo      DateWorked      EmployeeName      timeStart      timeEnd
6      2      Sat 13/12/2014      Sue      09:00      12:00
4      1      Sat 13/12/2014      John      09:00      12:00
3      1      Sat 13/12/2014      John      12:30      15:00
5      1      Sun 14/12/2014      John      09:00      17:00
2      1      Wed 17/12/2014      John      12:30      15:00
1      1      Wed 17/12/2014      John      10:00      12:00

I get these results:
JobNo      EmployeeName      DateWorked      Hours      Dayname      BillableHours
1      John      Sat 13/12/2014      05:30      Sat      10:00
2      Sue      Sat 13/12/2014      03:00      Sat      05:00
1      John      Sun 14/12/2014      08:00      Sun      16:00
1      John      Wed 17/12/2014      04:30      Wed      04:30
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 41754648
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now