time checking...

[stag246]

Hello here I used your query and changed it a little bit:

SELECT timecheck.id, timecheck.date, timecheck.empid, timecheck.fromtime,
timecheck.totime
FROM timecheck
WHERE (((timecheck.fromtime)<(SELECT MAX(T.ToTime)
FROM timecheck as T
WHERE T.Date = timecheck.Date AND T.FromTime <
timecheck.FromTime)))
ORDER BY timecheck.date;


and here is some of the results. which look like none of them really have
any issues? Did i not do something correctly when I changed your query?
thanks for all the help so far.




id empid date fromtime totime
380808 AdamsR 11/17/2004 14:00 16:30
380815 AdamsR 11/17/2004 9:00 13:00
380810 AdamsR 11/18/2004 14:00 16:30
380816 AdamsR 11/18/2004 9:00 13:00
380817 AdamsR 11/19/2004 8:15 12:15
378014 adkinB 11/15/2004 13:00 14:30
378050 adkinB 11/15/2004 15:30 18:00
378013 adkinB 11/15/2004 7:15 8:45
378036 adkinB 11/15/2004 21:00 22:00
378052 adkinB 11/16/2004 15:30 18:00
378015 adkinB 11/16/2004 7:15 8:45
378016 adkinB 11/16/2004 14:00 14:30
378037 adkinB 11/16/2004 9:15 12:15

We have a database (sql 2000) which we are going to start to pull
information from using MS access 2003. I am having trouble figuring
out what to do with this here.

A web-based form where coordinators enter times for attendants that
take care of consumers. Most attendants have more then one consumer
so they have to
fill out more then one time sheet to be entered into the web-based
form but there are some attendants that are putting a time say like
below that they would traveling from 2:15 til 3:00 and be at a
Consumer's place from 2:45 til 3:15 which means that they would be in
two places at once. Would there be a way I can set up a report or
something that would verify the time billed for that certain day so
that they were not double charging the company?

Here is what part of the table consists of:


time checking


id | date |consumerNO |fromTime | toTime |earnCode
1 | 3/1/2004 | 12 | 2:00 pm | 2:15 pm |Train
2 | 3/1/2004 | 12 | 2:15pm | 3:00 pm |Trvl
3 | 3/1/2004 | 14 | 2:45 pm | 3:15pm |Con
4 | 3/1/2004 | 14 | 3:30 pm | 3:45 pm |Train
5 | 3/1/2004 | 67 | 4:00 pm | 4:30pm |Trvl

Here's a query that I haven't tested out thoroughly, but that I think
should return any record that overlaps with one before it:

SELECT
tblWork.ID,
tblWork.WorkDate,
tblWork.ConsumerID,
tblWork.FromTime,
tblWork.ToTime,
tblWork.EarnCode
FROM
tblWork
WHERE
tblWork.FromTime<
(SELECT
MAX(T.ToTime)
FROM tblWork T
WHERE
T.WorkDate = tblWork.WorkDate AND
T.FromTime < tblWork.FromTime
);