I apologize for not making it clearer (my bad). I simplied the fields and
reposing the question.
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 pull this off in a query (or queries)
using MSAccess 2000?
--
Michaniker
Webmaster for PartMonster.com (
http://www.partmonster.com)
Tom Ellison said:
Dear Michaniker:
Do you actually have separate columns for the Start Date and Start Time? Or
have you placed these values in a single Date/Time column? If not, I'm
going to treat them as though they were in a single column. You may have to
combine them for this to work. So, I'll reference StartDate and EndDate as
though they contain the combined Date/Time value.
Another issue is that you don't show a column identifying one employee from
another. If you have such a column, you must adjust my response
considerably for that.
Now, in order to figure the time elapsed between Record 2 EndDate and Record
1 StartDate, you need to simultaneously reference to two different rows of
the table.
I'll reference your table name as YourTable, which you must change to the
actual name.
I will not assume the record numbers are sequential, or even in consecutive
order. In fact, I'll ignore this as being meaningless to the issue. Since
it can be solved without this, it is better not to rely on it.
SELECT StartDate,
(SELECT EndDate FROM YourTable T1
WHERE T1.StartDate = (SELECT MAX(StartDate)
FROM YourTable T2
WHERE T2.StartDate < T.StartDate))
AS PrevEndDate
FROM YourTable T
The above can be too difficult for Jet to run. It may be necessary to split
it up and make a saved query out of the inner portion. Let me know if you
have this difficulty.
Now, how does it work?
There are two nested subqueries. The "inner" subquery returns the StartDate
of the preceding shift worked, using StartTime to do this. It says to find
the maximum value of StartDate that is less than the target StartDate in the
outermost query (this is the point at which Jet often chokes).
Having obtained the StartDate of the preceding shift, the outer subquery
uses that date/time to retrieve the row from your table having that date,
and gives you the EndDate value from that row. That's the one you want in
your calculation. Now, this assumes you do not have duplicates in the
StartTime column. Otherwise, it will retrieve two rows as being the ones
with the most recent start time.
Now, it is possible to do this a little more simply. I have used StartTime
as being the column that places the rows in the order they are to be
considered. It is possible that EndDate also does this. In this case:
SELECT StartDate,
(SELECT MAX(EndDate) FROM YourTable T1
WHERE T1.EndDate < T.StartDate)
AS PrevEndDate
FROM YourTable T
Depending on your data, this may return the same result. If it does not
return the same thing, it may return a preferable result, or perhaps the
first query I gave would be preferable.
As it is simpler, I suggest you try this second way first.
Tom Ellison
