comparing fields

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

Guest

I have a table in in a time database which there is a date (date/Time format),
employee id (text format), fromtime(date/Time format), and totime(date/Time
format) fields. I want to have the query show any times which might overlap
another with the same employee on the same day. For example, empID= joe works
on 11/22/04 from 11:00am to 2:30pm at one site then travels to another site
and then he says that he worked again on 11/22/04 from 2:15am to 4:00pm.
would there be a way to have those records show up in a query? Is there
something
special I have to do?
 
Instead of using a separate date field, record both the date and time in
your FromTime and ToTime fields. This will simplify the calculations no end,
and will also make sense of the data if anyone ever works past midnight.

Once you have done that, the logic is that two records overlap if:
- A starts before B ends, and
- B starts before A ends.

You can therefore create a query that uses two copies of the same table in a
query with no join, and find the overlapping records.

Details in:
Clashing Events/Appointments
at:
http://members.iinet.net.au/~allenbrowne/appevent.html
 
Back
Top