Solved

Dates sorting by months

Posted on 2012-04-09
3
414 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

752 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