Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old December 2nd, 2005, 11:50 AM
Registered User
Join Date: Dec 2005
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default How To Calculate Monthy Total on-the-clock time

StartDate EndDate
1/1/2005 00:01:00 AM 1/1/2005 02:00:00 PM
1/1/2005 00:01:00 AM 1/1/2005 09:01:00 AM
1/1/2005 23:00:00 AM 1/2/2005 12:35:00 PM
1/28/2005 06:30:00 AM 1/31/2005 07:00:00 PM
1/31/2005 06:30:00 AM 2/01/2005 21:31:00 PM
2/3/2005 00:01:00 AM 2/04/2005 23:59:00 PM

Above is representative of a table (TimeTable) capturing my employee’s time on-the-clock. Each row represents the time a different employee clocked-in (StartDate) and clocked-out (EndDate).
I am attempting to pull the fields and calculate the total on-the-clock time, by month, in an attempt to show the total time that I do not have around-the-clock employee coverage. If the business is to run 24/7, this an important calculation so I can tell my boss how many more personnel I need to hire to get complete coverage. The difficulty I see in the rows are:

1. Some have overlapping times
2. Some have some equivalent clock-in time
3. Some could have equivalent clock-out times
4. Some EndDates exceed the end of month cut-off.
5. Some StartDates will preceed the start of a month.

For the above figures, I have an answer which was manually done.

-Jan 2005 has 31 days or 744 hours. Using the above table shows employee on-the-clock time as 117.05 hours therefore I do not have coverage for 626.95 hours.

-Feb 2005 has 28 days or 672 hours. Using the above table shows employee on-the-clock time as 69.47 hours.

What would the formula look like to get the total on-the-clock time, by month, in a query (or queries) using MSAccess 2000? I imagine this could be pulled off via query and pivot table.


Old December 2nd, 2005, 04:32 PM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

The first thing you might do is to create a totals query that totalled up the time by employee, so that times wouldn't overlap.

Then do a second totals query to extract and total the time totals per month with all employees' times.

This way there are no dupes.

Old December 2nd, 2005, 06:06 PM
Registered User
Join Date: Dec 2005
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts

But that wouldn't work. I'm not looking for the hours they work as a total. I am looking for the total time they work through the overlapping times.
For example, if I start work today at 1pm and work through 3pm and Pam starts work at 2PM and works through 4pm, there has only been three hours of coverage in time (1pm - 4pm). Your method would take my 2 hours and her two hours and get 4 hours.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Displaying Continuous Time (Clock) mjwelker VB How-To 4 April 8th, 2009 05:15 PM
Calculate running total on form and prevent text Hughesie78 ASP.NET 2.0 Basics 0 November 27th, 2007 10:35 AM
Calculate a running total Neal XSLT 3 March 22nd, 2007 11:16 AM
Calculate total hours based on Time deepcover1 Access 1 April 5th, 2006 01:50 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.