asked on

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.

regards,

David

ASKER CERTIFIED SOLUTION

nico5038

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chopper3226

ASKER

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

nico5038

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 !

Nic;o)