• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 274
  • Last Modified:

Query to show sum by day of week

Hi Experts,

I have the following table called [SickLeave]

With these fields

StaffId - PK (Relationship with table [Staff]
Sickdate - FK
hours - Number
Sicktype - FK Combobox
Formcompleted - Yes/No

I want to generate a report that will show me how many hours of total sickleave I have by day of week to discover any trends that may occur

The report will show

                            Monday        Tuesday    Wednesday etc.
Sick Leave hours    Sum(Hours)   Sum(Hours)   Sum(Hours)          

I don't know how to write the query to get this information.

I want to call the dates from a form callled "SickReportForm", using "Between [Forms]![SickReportForm]![FromDate] And [Forms]![SickReportForm]![ToDate]"

 I also want to create another report by staffID

                           Monday       Tuesday     Wednesday etc.
StaffID               Sum(Hours)   Sum(Hours)   Sum(Hours)  

I am comfortable that I can set the report up, but can't get a handle on the query.


  • 2
1 Solution
First create a query to return the day of the week like:

select format(sickdate,"ddd") as DateSick, * from SickLeave;

Use this query to create a crosstable query:
Place the fields:
StaffID,  DateSick and Hours
Change the query type to "Crosstable" and a row with "GroupBy" combo's and one for the "Crosstab" will appear.
For the StaffID set the "Crosstab" to "Rowheader", for the DateSick "Column Header" and for the Hours "Value"
Finally change the GroupBy under the Hours into: Sum

Save and run the query.

chopper3226Author Commented:
Thanks Nic, Took me a while but it is working beautifully
Guess the Crosstable query gave you a hard time, but they are very nice in this case to get the columns :-)

Success with your application !

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now