Summarize hours worked between M-F

[jbeets]

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.

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!

[ranman256]

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.

[jbeets]

Amazing! Thank you!

[jbeets]

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?

[ranman256]

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

[jbeets]

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!

[ArviLaanemets]

Or you get workdays as BETWEEN 2 AND 6, and weekends as IN (1,7)

[ArviLaanemets]

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

SELECT (Weekday([DateWorked],2)<6) AS [IsWorkday], SUM([Hours]) AS [TotalHours]
FROM tbl_WorkSummary
GROUP BY (Weekday([DateWorked],2)<6)

[jbeets]

Awesome! Thank you so much for helping me understand!