By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,930 Members | 1,465 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,930 IT Pros & Developers. It's quick & easy.

Validating a list of start & end times

P: n/a
I'm capturing the daily time employees spend on a multiple tasks for
billing. Can someone help me figure out how to confirm that the time
entered by each person daily follows two rules:
(1) no overlaps in time
(2) no gaps in time

good daily entry:
starttime-endtime
8:00 - 8:15 (task 1)
8:15 - 8:45 (task 2)
8:45 - 9:30 (task 3)

bad daily entry:
starttime-endtime
8:00 - 8:15 (task 1)
8:30 - 9:30 (task 2) (bad because of time gap)
9:15 - 10:15 (task 3) (bad because of time overlap)

I'm guessing I can start with a daily query for each employee and
process the data from the query.

I have no idea where to start looking for a function (or save the
function) that looks at one day's data to compare starttime = previous
endtime....

Can someone help please?
Thanks,
Debi

Jun 1 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Jun 1, 3:09 pm, debi.sny...@gmail.com wrote:
I'm capturing the daily time employees spend on a multiple tasks for
billing. Can someone help me figure out how to confirm that the time
entered by each person daily follows two rules:
(1) no overlaps in time
(2) no gaps in time
sniped
>
I'm guessing I can start with a daily query for each employee and
process the data from the query.

a subquery will be your friend.

with a table like EmployeeTimes(employeeID, workDate, start, end) try
something like following query:

SELECT employeeID, workDate, start, Times.end,
(Select Max(t.end)
FROM EmployeeTimes T
WHERE T.employeeID = EmployeeTimes.employeeID
AND T.workDate = EmployeeTimes.workDate
AND t.start < EmployeeTimes.start) AS previousEnd,
IIf(EmployeeTimes.start =
Nz([previousEnd],EmployeeTimes.start),"good","bad") AS looks
FROM Times
WHERE workDate = [someDate];

The trick is in 5th column, named previousEnd, that gets the previous
end time (the max(end) less than the current one) in an employee&date
basis.
The 6th column, named "looks" (!), evaluate the subquery results
(actually the previous end time) against the current start time.
Notice that a Nz() function uses the current start time for every
first row in a employee&date set to avoid a bad (null) value.
Hope this (and my English) help.
spier

Jun 1 '07 #2

P: n/a
On Jun 1, 12:18 pm, spier <spie...@yahoo.comwrote:
On Jun 1, 3:09 pm, debi.sny...@gmail.com wrote:
I'm capturing the daily time employees spend on a multiple tasks for
billing. Can someone help me figure out how to confirm that the time
entered by each person daily follows two rules:
(1) no overlaps in time
(2) no gaps in time

sniped
I'm guessing I can start with a daily query for each employee and
process the data from the query.

a subquery will be your friend.

with a table like EmployeeTimes(employeeID, workDate, start, end) try
something like following query:

SELECT employeeID, workDate, start, Times.end,
(Select Max(t.end)
FROM EmployeeTimes T
WHERE T.employeeID = EmployeeTimes.employeeID
AND T.workDate = EmployeeTimes.workDate
AND t.start < EmployeeTimes.start) AS previousEnd,
IIf(EmployeeTimes.start =
Nz([previousEnd],EmployeeTimes.start),"good","bad") AS looks
FROM Times
WHERE workDate = [someDate];

The trick is in 5th column, named previousEnd, that gets the previous
end time (the max(end) less than the current one) in an employee&date
basis.
The 6th column, named "looks" (!), evaluate the subquery results
(actually the previous end time) against the current start time.
Notice that a Nz() function uses the current start time for every
first row in a employee&date set to avoid a bad (null) value.

Hope this (and my English) help.
spier
Thanks! I'll give it a try. I have a table very similar to your
suggestion that contains the values; however, I don't understand two
things in your code...What are "Times.end" and "t.end" ?

Jun 1 '07 #3

P: n/a

debi.sny...@gmail.com wrote:
Thanks! I'll give it a try. I have a table very similar to your
suggestion that contains the values; however, I don't understand two
things in your code...What are "Times.end" and "t.end" ?
Oh, my mistake. I began typing and changed the names in between! sorry
for that.

Times.end should read as EmployeeTimes.end, or simply end, as whe have
only one tabel in the main query level.

Now the T.end thing: as we are querying and subquerying the same
table, we have to tell to access what exactly we are trying! Thus, in
the subquery we have to use an alias to distinguish another instance
of the same table. We got it "renaming the EmployeeTimes table as T in
the the subquery:
...
(Select Max(t.end)
FROM EmployeeTimes T --read "FROM EmployeeTimes _AS_ T"
...
This way we can compare fields from both instances by referencing them
wiaccordingly - T for the inner instance and EmployeeTimes for the
external (main) instance.

Jun 1 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.