[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Query to show sum by day of week

Posted on 2006-05-10
3
Medium Priority
?
269 Views
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.

regards,

David
0
Comment
Question by:chopper3226
  • 2
3 Comments
 
LVL 54

Accepted Solution

by:
nico5038 earned 2000 total points
ID: 16653924
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.

Nic;o)
0
 

Author Comment

by:chopper3226
ID: 16654320
Thanks Nic, Took me a while but it is working beautifully
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16655624
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)
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

834 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