I hit a mental block...if someone could help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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


Thanks,

Jeremy
 
stag246 said:
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
Click to expand...


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
);
 
Back
Top