Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2014
    Posts
    1

    Unanswered: Timesheet Billing Rates

    Hello,

    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?

  2. #2
    Join Date
    Nov 2012
    Posts
    32
    Hi Aiden,

    this looks like quite a challenge to do in Access. I guess I would probably struggle with this query even on SQL server.

    My advice is to use VBA procedure to get the results. I would probably go the way of temporary table and "splitting" the rows.

    So, from table like this:

    User ----- Job ----- Hours
    John --- Coding ------6

    you would get table like this:

    User ----- Job ----- Hours ----- Rate
    John --- Coding ----- 2 -------- 0.5
    John --- Coding ----- 4 --------- 1

    You would need to have some fun with variables when handling overlapping jobs, but that shouldn't be difficult.

    Hope that helps a little.

    Marek

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •