Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Dates sorting by months

Posted on 2012-04-09
3
Medium Priority
?
422 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 1400 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 600 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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.

705 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