Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2004
    Posts
    156

    Unanswered: Summing By Weeks

    Happy New Year All!

    I'm having an issue with something that only arises at most twice a year. I'm finding labor cost by summing the hours per week. 40 and under gets regular pay rate, 40+ gets OT pay rate. I do this by using the function DatePart("ww",[Date],1,3). I use 3 (first full week of year is first week) so that there will be no "partial weeks" numbered as a week, as there is with the default first week with January 1. For example, at the beginning of next year, using the first week is the one with January 1, there will be only one day in the week #1 of 2005 and only 6 days in week #53 of 2004. Therefore, to sum up hours by week, I have to use an option that will only give whole weeks.

    The problem with this is that it will leave a partial week at the beginning of the year that will count as part of the last year's last week. For example, the end of this year coming up and the beginning of next year will be tied together as the 52nd week of 2004 (12/26/04-1/1/05). Then 1/2/05 will start week 1 of 2005.

    Essentially, this would put some labor hours in the previous year than when they were actually worked. If I wanted to pull the labor cost for a particular year, how could I include the hours from the last few days in December for the purpose of seeing if I'm over 40 hours for that week? See what I'm saying? Because those hours would be included in any Overtime hour calculations. Am I approaching this from the wrong angle of using week #'s to sum labor hours?

    This seems to be a complicated problem that surely thousands of programmers have had to deal with. Every payroll program would have to deal with this. Your help on this would be GREATLY appreciated!
    Last edited by DocX; 12-29-04 at 04:30.
    DocX

    The teachings of God's Begotten: 2 John 1:9

  2. #2
    Join Date
    Jul 2004
    Posts
    156
    bumping for the Holiday lull.
    DocX

    The teachings of God's Begotten: 2 John 1:9

  3. #3
    Join Date
    Jul 2004
    Posts
    156
    bumping again...just because I don't have an answer.
    DocX

    The teachings of God's Begotten: 2 John 1:9

  4. #4
    Join Date
    Dec 2004
    Location
    Sunshine Coast, Australia
    Posts
    52
    SELECT SUM(HOURS) FROM table WHERE DATE BETWEEN DATE1 AND DATE2

    Thats how you could do it, personally I do everything mathmatical behind VBA

  5. #5
    Join Date
    Jul 2004
    Posts
    156
    Umm...no. Thanks for the attempt. But it's far from what I need. I wish it were that easy. I'm working with queries, anyway, which don't use VBA. You have to use SQL. See, I need to sum hours that aren't included in the date range, as well, based on a week-by-week basis.
    DocX

    The teachings of God's Begotten: 2 John 1:9

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    I'm just not understanding your problem ... Perhaps you're making this too complicated ... It sounds like you're trying to calculate hours chronically using financial rules ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Jul 2004
    Posts
    156
    Here it is in all its basicity: I need to figure up the cost per week per employee for any given date range. My query is based on the date range they put in. So, a start date in the middle of a week will not include the hours from the beginning of that week, which will cause problems when summing up the employee's work hours to determine if OT was worked. I sum up hours by grouping the records (filtered by date range) by week #. I don't know how to include the hours from the beginning of the week without also including them in with the labor cost.

    Example: Boss wants labor cost of hourly employees for dates 1/5/05 - 1/31/05. 1/2/05 - 1/4/05 will not be included in the query as I have it now. However, the hours worked for those days will be necessary to determine when OT (over 40) will hit, which will drastically change the labor cost for that week.

    If you have any more questions, let me know. Thanks for givin' me an ear!
    DocX

    The teachings of God's Begotten: 2 John 1:9

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Ok ... How to you determine which hours constitute OT? Is it after 40? After 8 per day? After a certain time of the day?

    As for the query: I don't see any way of making a "generic" query to accomplish this ... You might with a 2 parter ... Run a query to calc your labor cost for the week and then run a query to subtract out either the prior year's amount or the current year's amount (depending on with side of the equation you're examining - also depending on your OT rules) ...
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You have submitted two mutually exclusive criteria. First you say that no partial weeks are to be allowed in the calculation, then you say you must calculate partial weeks so they are not counted twice.

    Am I missing something?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Here's something that should get you pointed in the right direction:

    PARAMETERS StartDate DateTime;
    SELECT employeeid, SUM(BEGTTLHOURS) AS TTLHOURS, SUM(WTDTTLHOURS) AS WTDHOURS
    FROM (
    SELECT employeeid, 0 as WTDTtlHours, Sum(hoursworked.hours) AS BegTtlHours
    FROM employees INNER JOIN hoursworked ON employees.employeeid = hoursworked.employeeid
    WHERE (((DatePart("ww",[date],1,3))=DatePart("ww",[StartDate],1,3)))
    group by employeeid
    union
    SELECT employeeid,Sum(hoursworked.hours) AS WTDTtlHours, 0 as begttlhours
    FROM employees INNER JOIN hoursworked ON employees.employeeid = hoursworked.employeeid
    WHERE (((DatePart("ww",[date],1,3))=DatePart("ww",[StartDate],1,3)) AND ((hoursworked.date)>=[startdate]))
    group by employeeid)
    group by employeeid;

    I created a table with 3 columns: employeeid, date, and hours
    The parameter supplied to the query, which you'll be asked for when you open the query (StartDate) is the beginning date for which you are totalling hours.
    The first half of the UNION subquery will supply total hours worked by employee for the week containing that date (BegTtlHours).
    The second half of the UNION subquery will supply hours worked in that week where the date is >= the StartDate paremeter.

    You'll have to add a second parameter to your query (EndDate) and perform the same steps for the last week. Last, you'll have to total the hours for all weeks in between. I think this is possible in one giant query, with multiple union subqueries.

    Hope that helps get you started.
    Last edited by RedNeckGeek; 01-03-05 at 11:24.
    Inspiration Through Fermentation

Posting Permissions

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