Query to show sum by day of week

Posted on 2006-05-10
Last Modified: 2012-06-27
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.


Question by:chopper3226
    LVL 54

    Accepted 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.


    Author Comment

    Thanks Nic, Took me a while but it is working beautifully
    LVL 54

    Expert Comment

    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 !


    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now