Solved

Dates sorting by months

Posted on 2012-04-09
3
421 Views
Last Modified: 2012-04-09
Hello experts,

i have a table that is having a a daily collection of numbers of employee hours, and I want to create a query that will give me a sum of total hours of employees sorted by months,

below is the sample of query that i want to create

Jan-12      11904
Feb-12      11376
Mar-12      10912

here is the sample data that i have in the table

     Date           noe;      Hours accumulated
01/01/2012      50         400
02/01/2012      50    400
03/01/2012      50    400
04/01/2012      50      400
05/01/2012      50      400
06/01/2012      60      480
07/01/2012      50      400
08/01/2012      50      400
09/01/2012      50      400
10/01/2012      50      400
11/01/2012      45      360
12/01/2012      50      400
13/01/2012      50      400
14/01/2012      50      400
15/01/2012      13      104
16/01/2012      50      400
17/01/2012      50      400
18/01/2012      50      400
19/01/2012      50      400
20/01/2012      50      400
21/01/2012      50      400
22/01/2012      20      160
23/01/2012      50      400
24/01/2012      50      400
25/01/2012      50      400
26/01/2012      50      400
27/01/2012      50      400
28/01/2012      50      400
29/01/2012      50      400
30/01/2012      50      400
31/01/2012      50      400
01/02/2012      50      400
02/02/2012      50      400
03/02/2012      50      400
04/02/2012      50      400
05/02/2012      35      280
06/02/2012      50      400
07/02/2012      50      400
08/02/2012      50      400
09/02/2012      50      400
10/02/2012      50      400
11/02/2012      50      400
12/02/2012      50      400
13/02/2012      23      184
14/02/2012      50      400
15/02/2012      89      712
16/02/2012      50      400
17/02/2012      67      536
18/02/2012      50      400
19/02/2012      45      360
20/02/2012      50      400
21/02/2012      67      536
22/02/2012      50      400
23/02/2012      56      448
24/02/2012      50      400
25/02/2012      34      272
26/02/2012      50      400
27/02/2012      6      48
28/02/2012      50      400
29/02/2012      50      400
01/03/2012      5      40
02/03/2012      50      400
03/03/2012      10      80
04/03/2012      50      400
05/03/2012      50      400
06/03/2012      13      104
07/03/2012      50      400
08/03/2012      20      160
09/03/2012      50      400
10/03/2012      50      400
11/03/2012      50      400
12/03/2012      29      232
13/03/2012      50      400
14/03/2012      75      600
15/03/2012      50      400
16/03/2012      56      448
17/03/2012      50      400
18/03/2012      25      200
19/03/2012      50      400
20/03/2012      34      272
21/03/2012      50      400
22/03/2012      67      536
23/03/2012      50      400
24/03/2012      89      712
25/03/2012      50      400
26/03/2012      78      624
27/03/2012      50      400
28/03/2012      31      248
29/03/2012      50      400
30/03/2012      32      256

I hope that you will help me with this

Thanks...

Czher
0
Comment
Question by:Czher
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 350 total points
ID: 37823216
try this query


SELECT Format([Date],"mmm-yy") AS dDate, Sum(yourTable.[hours]) AS [Hours accumulated]
FROM yourTable
GROUP BY Format([Date],"mmm-yy"), Format([Date],"yyyymm")
ORDER BY Format([Date],"yyyymm")
0
 
LVL 22

Assisted Solution

by:Om Prakash
Om Prakash earned 150 total points
ID: 37823229
Try the following:

SELECT Year(DateField), Month(DateField), Count(Hours_accumulated) FROM Table_Name
 GROUP BY Year(DateField), Month(DateField)
0
 

Author Closing Comment

by:Czher
ID: 37823295
those answers are both accepted

Thanks.....
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

617 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