G
Guest
How To Calculate the Total on-the-clock time, by Month, in MSAccess 2000
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.
--
Michaniker
P.S. This was answered once before but the thread was deleted before I got a
change to try the results.
Webmaster for PartMonster.com (http://www.partmonster.com)
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.
--
Michaniker
P.S. This was answered once before but the thread was deleted before I got a
change to try the results.
Webmaster for PartMonster.com (http://www.partmonster.com)