Results 1 to 9 of 9
  1. #1
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    52

    Summarize hours worked between M-F

    Hello!



    I have a query that currently calculates the total number of hours worked by day of week. What I need the query to do is summarize for each month the hours worked between M-F and separately between Saturday and Sunday.

    Code:
    TRANSFORM Sum(tbl_WorkSummary.[Hours]) AS [Total Hours]
    SELECT WeekdayName(Weekday([DateWorked],1)) AS [Day of Week]
    FROM tbl_WorkSummary
    GROUP BY WeekdayName(Weekday([DateWorked],1))
    ORDER BY WeekdayName(Weekday([DateWorked],1))
    PIVOT Format(Month([DateWorked]));
    
    Does anyone have a suggestion on what formula I would use to make this work?

    Thank you for any help you can provide!

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,200
    in your base query (not the crosstab) get the fields needed and add:
    format([dateFld],"w")
    this will give you the day of week; 1=sunday, 7= Saturday,
    so set the criteria under this field to: between 2 and 6

    then run the crosstab on the query above.

  3. #3
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    52
    Amazing! Thank you!

  4. #4
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    52
    Quote Originally Posted by ranman256 View Post
    in your base query (not the crosstab) get the fields needed and add:
    format([dateFld],"w")
    this will give you the day of week; 1=sunday, 7= Saturday,
    so set the criteria under this field to: between 2 and 6

    then run the crosstab on the query above.
    Ok one more quick question - for Saturday/Sunday, I cant use Between seeing as Sunday is 1 and Saturday is 7. What would I use in this case?

  5. #5
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,200
    between 2 and 6 is inclusive. it will work.

    you dont want 1 & 7 since those are weekends.
    if you want weekends, then: 1 or 7

  6. #6
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    52
    Thank you again! So OR will not pull either Saturday OR Sunday but instead I can use it to pull both Sat and Sunday only? For this project I need to be able to report the hours worked during the week and separately on the weekend so I just want to make sure I understand how that works.

    I truely appreciate your help!

  7. #7
    Join Date
    Apr 2017
    Posts
    1,101
    Or you get workdays as BETWEEN 2 AND 6, and weekends as IN (1,7)

  8. #8
    Join Date
    Apr 2017
    Posts
    1,101
    Or you get workdays as BETWEEN 2 AND 6, and weekends as IN (1,7)

    Or you use instead
    format([dateFld],"w", 2)
    and then you get
    workdays as < 6, and weekends as > 5

    Or you base your end query on
    Code:
    SELECT (Weekday([DateWorked],2)<6) AS [IsWorkday], SUM([Hours]) AS [TotalHours]
    FROM tbl_WorkSummary
    GROUP BY (Weekday([DateWorked],2)<6)
    
    

  9. #9
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    52
    Awesome! Thank you so much for helping me understand!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Consolidating Hours Worked in Day Query
    By blimbert in forum Queries
    Replies: 9
    Last Post: 01-15-2020, 07:54 PM
  2. Sum of worked hours
    By infratunes in forum Access
    Replies: 9
    Last Post: 10-24-2018, 12:29 AM
  3. Replies: 8
    Last Post: 03-01-2013, 11:55 AM
  4. calculate rate * hours worked
    By hamish mather in forum Queries
    Replies: 1
    Last Post: 12-22-2011, 06:14 AM
  5. Sum hours worked, grouped by dep't
    By goodfood in forum Queries
    Replies: 3
    Last Post: 05-05-2011, 06:11 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums