Solved

Calculating total hours worked?

Posted on 2010-08-15
2
479 Views
Last Modified: 2013-11-28
I am trying to calculate the total hours worked per individual but I'm unsure how to do this with a query.

For instance, Table 1 may look like this:

ID         EmployeeID             DTStart                                DTEnd                                 Total Hours?
1          1234                      15/08/2010 11:30:00            15/08/2010 18:45:00
2          5678                      16/08/2010 08:45:00            16/08/2010 18:45:00
3          5678                      16/08/2010 22:10:00            17/08/2010 02:30:00
4          3456                      16/08/2010 22:00:00            16/08/2010 23:30:00
5          5678                      18/08/2010 18:00:00            19/08/2010 01:30:00


If for example I wanted to know the total hours for employee 5678, how could I work this out in an access query?  I need two results, the first, total hours each instance, and the second total hours combined for each employeeID?

Any ideas would be greatly appreciated thanks,

Jon
0
Comment
Question by:JonB1984
2 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 33440763
Assuming DTStart and DTEnd are both using date/time data type, in Access:
SELECT EmployeeID, Sum(DTEnd - DTStart) * 24 AS HrsWorked

FROM [Table 1]

GROUP BY EmployeeID

Open in new window

0
 

Author Closing Comment

by:JonB1984
ID: 33440922
This works brilliantly for the first part of my question and I can get the rest sorted myself now.

Thanks,

Jon
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

932 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now