I have an Access 2013 DB with a table "Timesheets". Within that table i have fields for "Date", "Start Time", "Stop Time", "Lunch", "Job" and "Employee".
I am trying to create a field within a query which will calculate the amount of hours which will be billed at two different rates on a Saturday. For the first two hours it is billed at a lower rate, and a higher rate there after.
My problem is that an employee may work 3 jobs on the same day and i need only the first two hours of the day to be billed separately. I need a way to check to see if the employee has worked two hours on any job and depending on that outcome to bill at the according rate.
Is there a way i can apply the reduced rate only to the first two hours even if it may span over two different jobs?