Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Oct 2012
    Posts
    11

    Question Unanswered: Query to calculate hours worked per day.

    Hi all, I'm hoping someone can help me out with this, or at least point me to a specific post where this has already been covered. I'm sure this is probably very simple, and I'm just overthinking it.

    I've been tasked with building a payroll database in Access 2007 for our per diem staff. They can work on multiple cases in one day, and some of those cases do carry over into the next day. Also, multiple staff members can work on the same case, so there would be multiple entries for the same case, which links to the main Case table in a (many-to-one relationship). The table for their hours is called Time Card Hours, and is set up as follows:
    CaseID - text
    StaffCode - text
    DateIn - short date
    TimeIn - long time
    DateOut - short date
    TimeOut- long time

    Example: Tech1 starts a case at 8:30 PM on 10-20-2012 and finishes at 3:00 AM on 10-21-2012. Tech1 then starts another case at 10:45 AM and finishes at 4:00 PM. Tech1 then logs time from 6:00 PM to 7:30 PM on paperwork, before getting one last case, starting at 11:10 PM, and ending at 4:35 AM on 10-22-2012.

    I've been able to successfully calculate the total time spent on each case, even spanning midnight, with DateDiff, but I haven't been able to figure out how to calculate the total time spent working each day by the tech. This is extremely important because California law dictates that overtime be applied if more than 8 hours are worked in any given day. Would I be better off doing these calculations in a report instead of a query? Is this possible with the way I have my table currently structured? I did add fields for the start and end date of each day (12:00:00 AM and 11:59:59 PM), but don't know if it's of any help. I'm really in a bind here, can anyone help me out please?

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    My rule of thumb is to NEVER use a many-to-one relationship, period.

    Use three tables. 1-personnel, complete with pk, names, etc.

    2-cases, complete with pk, etc.

    3-worktime, with personnelPk, casesPk, workdate, timestart, timeend, and any other info you need.

    Each day would be entered into separate records, including start and end times, so you can have your day detail as necessary.

    Relationships-both tblPersonnel and tblCases should be related to tblWorktime with one-to-many relationships.

    This scheme should make your work easier.

    Sam

  3. #3
    Join Date
    Oct 2012
    Posts
    11
    Thanks Sam, I've made the changes to my table structure based on your suggestions, and re-entered some test data. I'm still running into the problem of getting accurate time worked per day per person, especially when the time worked spans midnight, and the person works mutliple cases in a day. Should I be grouping on the date started? Any suggestions?

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    With my scheme, any worktime that spans midnight would be entered as a separate day; meaning end of work on day 1 would be midnight, and beginning of work for day 2 would also be midnight. Each period would need a separate record of data entry. Obviously inefficient! What to do?

    Try this. Change the field name 'workdate' to 'wstartdate.' Add another field called 'wenddate' (both of them date fields, obviously). It would probably be efficient to add 3 more fields (in the table, but hidden on the form) called 'wtotaltime,' 'wDay1,' and 'wDay2' in time format. When you save the entered record, (perhaps with a command button), have VBA calculate the time worked as follows:

    SAMPLE CODE (for the command button's OnClick event)
    Code:
        Dim STime as Date, ETime as Date
    
        STime = Me!wstartdate + Me!timestart
        ETime = Me!wenddate + Me!timeend
    
        Me!wtotaltime = ETime - STime
    
        ' The rest of this code is in order to comply with the California state requirement.
    
        If Me!wstartdate = Me!wenddate Then
            Me!wDay1 = Me!wtotaltime 'wDay2 remains blank
        Else '00:00 in the following lines indicates midnight
            Me!wDay1 = (Me!wenddate + #00:00#) - STime
            Me!wDay2 = ETime - (Me!wenddate + #00:00#)
        End If
    Now proceed to save the record.

    You now have complete data, including: total time (on this project only, of course), amount per day1, and amount per day2 (if/when applicable).

    The last fields are for the purpose of calculating any necessary overtime on a report. Since each project time is entered separately, it is not immediately obvious who has overtime on a daily basis and who doesn't. You would want to run a report every day to resolve this question.

    Sam

    PS DateDiff() is incorrect to use, as the function will only give hours (rounded, not exact) OR minutes (which you then have to divide by 60, etc.), not both.

  5. #5
    Join Date
    Oct 2012
    Posts
    11
    Thanks Sam, the code you supplied is working as designed, and my test case times are populating the correct fields. I knew I'd be creating a report at some point, but what would be the best way to group the information so it all adds up properly? I was originally thinking the start date, but then I wasn't sure how to exclude the end date time value when the start and end dates were different. Any suggestions?

    I had been using DateDiff to minutes, then dividing by 60, displaying the value to two decimal places because it produced a number that could be better multiplied by a pay rate to produce a more-accurate pay amount, like this:

    [Time Card Hours].NonCaseHours = IIf([Time Card Hours].[WorkCodeID]=13,Round(DateDiff("n",[DateIn]+[TimeIn],[DateOut]+[TimeOut])/60,2),0)
    [Time Card Hours].NonCasePay = IIf([Time Card Hours].[WorkCodeID]=13,([NonCaseHours]*[AllStaff].[NonCaseRate]),0);

    If I tried multiplying actual time values times the currency, the numbers were coming out way low, but I didn't spend a whole lot of time trying to figure out why. I see how DateDiff is not the most accurate, but if there is a way to multiple dollar amounts by time values, I haven't found it.

    Cris

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    If I tried multiplying actual time values times the currency, the numbers were coming out way low, but I didn't spend a whole lot of time trying to figure out why.
    The reason is because date/time data is stored in Access as a real number, date info to the left of the decimal, time data to the right. E.g. #10/24/2012 06:00# (6 am this morning) is actually stored as 41206.25. 41206 is the amount of days since 1/1/1900, and .25 is 1/4 day from midnight until the following midnight. Hence, if you're multiplying, for example, 10 hours times $15 per hour, you won't get $150, but (10/24) X 15 or $6.25.

    If you want to use the time data to calculate the pay, you need to multiply it by 24. In the previous example, then, that would equal (10/24) X 24 X 15, or $150.

    BTW, this is just to answer your question. I still have other issues I want to address before giving you a comprehensive answer on your main problem. I may not get to it right away, because I'm busy this minute, but I'll try later on.

    Sam

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Cris,

    Although you tell me that
    the code you supplied is working as designed, and my test case times are populating the correct fields
    I am not confident that the data is actually populating the fields in the table. Please post a few records, along with the field names, so I can get a good look at the actual table data. Please include some records that span midnight, and some that don't. When you post the records, use the 'quote' feature so the text won't wrap. You can 'dumb' the records down, if you feel you need to. If everything is working (although I have my suspicions) I can work on the report question.

    Sam

  8. #8
    Join Date
    Oct 2012
    Posts
    11
    OK, here are some of the records:

    Time Card Detail ID StaffCode DateIn TimeIn DateOut TimeOut wtotaltime wDay1 wDay2
    182 BS 10/23/2012 3:25:00 AM 10/23/2012 4:25:00 PM 13:00 13:00
    183 GME 10/23/2012 10:05:00 PM 10/24/2012 7:50:00 AM 9:45 1:55 7:50
    184 GME 10/24/2012 8:55:00 AM 10/24/2012 9:35:00 AM 0:40 0:40
    185 GME 10/16/2012 8:00:00 AM 10/16/2012 9:20:00 AM 1:20 1:20
    Time Card Detail ID StaffCode DateIn TimeIn DateOut TimeOut wtotaltime wDay1 wDay2
    195 BB 9/30/2012 10:30:00 PM 10/1/2012 4:00:00 AM 5:30 1:30 4:00
    196 JCC 9/30/2012 10:00:00 PM 10/1/2012 3:00:00 AM 5:00 2:00 3:00
    As far as I can tell, everything is working properly, but maybe I'm missing something.

    I was also discussing this with a former collegue, and he offered me some code. It required me to create two additional columns: [DTI] and [DTO], both of which combine the date and time into one field. There's a lot of re-formatting of dates and the use of DateDiff because the values come out rounded, but the output is fairly close to what I'm looking for. Is it something that could be incorporated with some minor tweaking?

    Code:
    SELECT tmp.StaffCode, tmp.WorkDate, Sum(tmp.HoursWorked)/60 AS SumOfHoursWorked
    FROM (SELECT StaffCode,
    format(dti,"yyyy/mm/dd") as WorkDate,
    iif(format(dti, "Small Date") = format(dto, "Small Date"),
    DateDiff("n",[dti],[dto]),
    datediff("n",[dti],format(dateadd("d",1,[dti]),"yyyy/mm/dd 00:nn:ss"))
    ) as HoursWorked
    FROM [Time Card Hours]
    union all
    SELECT StaffCode,
    format(dto,"yyyy/mm/dd") as WorkDate,
    iif(format(dti, "Small Date") = format(dto, "Small Date"),
    Null,
    datediff("n",format([dto],"yyyy/mm/dd 00:nn:ss"),[dto])
    ) as HoursWorked
    FROM [Time Card Hours]
    )  AS tmp
    GROUP BY tmp.StaffCode, tmp.WorkDate;
    Lastly, and this is probably more of a general question, but since my last post, my version of Access got upgraded from 2007 to 2010. Are there any changes in 2010 that might make this problem easier to solve?

    Thank you again for all your help, Sam. I know you are busy, but I greatly appreciate the time you are taking to help me.

    Cris

  9. #9
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Sorry, Cris, but I don't use 2010, I use 2003. However, I assume the code is compatible. Microsoft usually doesn't invalidate code when they upgrade programs; they make upgrades backwards compatible.

    It really does look like the information is correct. If everything's working, there's no reason to use other code, or to use datediff(), which is necessarily rounding the data. I just don't see the point of using functions that don't work for you when you can use simple arithmetic to find the exact time in one step.

    I looked over your friend's code briefly. I'm not sure what he's doing. Is that the report he's programming, or just putting the info into the table in the first place?

    If you're ready to tackle the report with me, I'll be happy to continue.

    Sam
    Last edited by Sam Landy; 10-25-12 at 18:02. Reason: took another look...

  10. #10
    Join Date
    Oct 2012
    Posts
    11
    I think he was just throwing out a query he thought would work, and it actually came pretty close. When I tested it using some sample data, it produced the following results:
    SumOfHoursWorked - Actual Hours from timesheet
    (from query) -
    4.00 - 3.50
    16.83 - 17.57
    9.08 - 9.08
    6.00 - 6.25
    11.67 - 11.63
    12.50 - 12.48
    11.00 - 11.00
    1.00 - 1.58
    I'm sure a lot of the rounding came from the DateDiff (some of the smaller differences in numbers are likely due to the way I was entering data in the first place, using some timeclock code that was set with 5-minute intervals instead of being to the exact minute. My payroll person approved of this method), but it was still closer than anything I had come up with to that point. However, I haven't been able to successfully tweak it to get more accurate numbers. If we can achieve the proper numbers in the report, I'm fine with that too, so I'm ready to work on that. Let me know what information you need. Thanks again.

    Cris

  11. #11
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    How about if I give you the concept, and you come up with the query/report?

    Make a query consisting of fields StaffCode, DateIn, DateOut, wDay1, wDay2 WHERE (StaffCode = some combobox value) and (DateIn = (the value of a combobox entry) Or DateOut = (the value of the combobox entry)). Of course, you can design it in either Access' query designer or directly in VBA.

    You may want to include the table that contains the staff hourly rate, etc. in the query, and make an inner join to it. Include pertinent field(s) in the query from this table as well.

    Now that you have your subset of data, you have a choice of how to display/report the time * rate info; you can report on it on a daily basis or once, at the bottom line of the report. Just make sure that whatever time you're using has the correct datein or dateout. Also, you have to check for dateout being empty.

    You can, if you wish, have from-to comboboxes and report on a whole week (or whatever) at a time.

    Sam

    BTW, it seems to me you're missing job info from the table. This info is critical to payroll accounting, as they have to know how much to charge each job. The query for the report must include it as well.

    Also, there's nothing unusual about having VBA code on a report, just like on a form.

  12. #12
    Join Date
    Oct 2012
    Posts
    11
    Sorry Sam, I didn't mean to give you the impression I wanted you to do this for me. That wasn't my intent.

    For the report queries, I was going to set certain parameters in the query itself for Staff Code, and Start and End Date, so the user will be prompted to enter those to get the data for that specific tech and date range (Also, dateout can never be empty, I made it a required field so that there would be a definitive end time). I was thinking of the report containing a main and sub report. The main report would be to display the overall totals per day: total case hours worked that day, regular hours, overtime hours, double-time hours, and non-case hours with a sum total line that would include hourly wage (total case hours for the week/total case pay), the calculated pay associated with OT, 2X, and Non-case (where applicable), as well as a grand total (all calculated within the report). The sub report would be the specifics for each day: hours worked per case number, individual case rates, etc. Maybe I'm making this too complicated?

    My problem continues to be how to display/calculate specific hours per day. To go back to the Tech1 example in my original post, how do I get this data calculated/displayed so that it comes out as this:
    Date Worked - Total Hours:
    10-20-2012 - 3:30
    10-21-2012 - 10:35
    10-22-2012 - 4:35
    When the Tech enters their time as:
    DateIn - TimeIn - DateOut - TimeOut:
    10-20-2012 8:30 PM 10-21-2012 3:00 AM
    10-21-2012 10:45 AM 10-21-2012 4:00 PM
    10-21-2012 6:00 PM 10-21-2012 7:30 PM
    10-21-2012 11:10 PM 10-22-2012 4:35 AM
    Once I have those total hours, pretty much everything else falls into place. Maybe I missed something here on how to get that even though the VBA code you provided does produce the proper times before/after midnight. I'll go ahead and work up the queries, and see what happens.

    As for job info, each tech's case rates are stored in the AllStaff table. I have a seperate table for Job Functions linked to Time Card Hours via a WorkCodeID. I have several IIF statements in place that will set a tech's case rate based on their job function on that specific case, as well as their non-case rate if the hours are not related to a case. Billing out for a specific job or case is not the function of this database, only to better collect and report time logged by our per diem staff (right now their hours are recorded on Excel spreadsheets, and case info is going on an InfoPath form (which they may or may not log their time on), which then have to be cross-matched.

  13. #13
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I don't mean to derail you, but if you don't mind I'd like to make a comment about reports containing sub-reports. I know from reading questions on this forum that they're quite popular. I tried it once, to see what it was like, and I hated it immediately so I went back to reporting details first, summaries and totals further down the report. Know why? It's because, besides coming up with all the details in an underlying query, you also have to come up with the totals and summary data, in a query or VBA, before you even get to report on the detail. What a load of extra work! I'm glad my bosses didn't require it. When you do it the other way, you come up with the the details as presented in the underlying query, you can even massage the data in VBA code, and then put the final data (still detail, mind you) into the report's 'detail section.' You then make a totals & summaries line (or sub-totals, etc.) and let Access' report generating software calculate that level of totals for you. Of course you can have several levels of totals in the same report; consider, in your case, you can total by tech, by day, by week. (By job code would require a diferent routine, though, because job crosses both tech and day lines. However, you could still throw some VBA at it, calculate it, and display a total for that as well.)

    If my boss would have insisted on the other way, I would have asked him if he would like two separate reports, one all details and one all totals and summaries. I would only go the sub-report way if I had no choice.

    Do you still want to go the sub-report way?

    Sam

  14. #14
    Join Date
    Oct 2012
    Posts
    11
    Doing a sub-report is not absolutely necessary. I was thinking it was the best way to display the data with the tech's pay period totals on the top, and the sub-report with the daily detail on the bottom (it also would mimic the current Excel format). I don't need to create totals based on specific job codes, fortunately, just the totals by tech, per day and week. I hadn't actually started designing the report yet, as I was still working on the queries for the staff, and since I've had no input regarding how it should look, I have flexibility there, so I'm open to suggestions. I'm more concerned that the information it presents is correct.

    Cris

  15. #15
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    The way I would do it is to build your underlying recordsource so that it includes all data for tech, day, and week. You can leave the actual sorting for the report generator. You may want to have records with different start and end dates as separate records in the recordsource.

    To my mind, for purposes of this report, whether an entry represents a start date or end date is not relevant, as you're mainly reporting on total data per day/week, as opposed to the minutiae of the actual work dates/times. Of course, that will be a separate report.

    Include all the details in the details section, make a sub-total line for day, and a total line for week and tech. If this is a monthly report (hey, why not?) the weekly line will be a sub-total line, and the totals line can be by tech and month.

    The simple way - meaning my way - would be to have a make-table query of ID info and all startdate/starttime data for records with startdate = the combobox entry. Make a second append query with ID info and all enddate/endtime data for records with enddate = the combobox entry. Of course you have to make sure and map this second query's target fields correctly.

    If you're using from-to combobox values, such as in a weekly/monthly report, this two-query scheme will ensure you're not ignoring valid dates that only appear in certain records as end dates, such as last week's (or last month's) records that have overlaps.

    Doing it this way, you only report on one column of time data. In the totals line, you can then multiply the total time by rate, and come up with a total weekly/monthly figure. Of course, you can make this calculation in any sub-total line as well, carrying it all the way down to the total line.

    Sam

    By the way, I apologize if you find my replies overly wordy. I find that the best way to present my thoughts, without coming across as "thou shalt...," is by accompanying my steps with an explanation, which someone else can then study and discuss/argue.

Posting Permissions

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