Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    6

    Question Unanswered: Divide total by week in query?

    Hello!
    I need to work out the overtime by week for a table of employees. The information that I have is hours worked per day and my query is set up to show only the latest month. So, I am able to total the hours worked for the month however, I want to work out what the overtime is and this needs to be worked out on a weekly basis, not daily or monthly. Any suggestions? I need to know what and where - only in query veiw please as the VB baffles me!
    Many thanks, in advance.
    Sheila1971

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    What does your table design look like?

    If you have something like:
    WorkDate Hours
    01-jul-2004 8
    02-jul-2004 9

    you can do

    Select Format(WorkDate,"WW",2,2) as WeekNR, Sum(Hours)
    from YourTable
    GroupBy Format(WorkDate,"WW",2,2)

    This will sum the worked hours total to a week.

    Greetz

  3. #3
    Join Date
    Jul 2004
    Posts
    6

    Thank you, but still confused...

    Hi, thanks once again for your help!
    However, I am confused a little - where you state 2,2 - I assume this refers to the number of rows and columns. What if I have an unknown number of rows and apx 15 columns?
    Thanks again,
    Sheila1971

  4. #4
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    No not to rows.... to setup the function... Read all about it in the access help...

    In short its needed to use the European weeknumbering and starting a week on monday....

    Greetz

Posting Permissions

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