Things happening at the same time

[James Carroll via AccessMonster.com]

Hello

I have searched discussions using search terms like "simultaneous",
"simultaneity", "events" and others, but haven't run across a discussion of
what I wish to do.

I have a database that keeps track of cases. Each case lasts from minutes
to hours, and has a Date field as well as TimeBegin and TimeEnd fields.
Recently I was asked to document the number of times that the cases
overlap, i.e., occur simultaneously or occupy the same slice of time. I'm
stumped.

The date field is in proper date format as is the time field (24-hour
format).

Just to make it more interesting, these cases sometimes span the midnight
hour, such that the TimeEnd might be smaller in absolute value than the
TimeBegin. I've gotten around that with some unwieldy query terms, but I
haven't tried it in the context of having a date & time query.

Can anyone point me in the right direction?

Thanks.

Jim

 

[Michel Walsh]

Hi,


Get the DATE associated with the TIME to avoid useless complexity.


There is no overlap between a and b if

a.start > b.end OR a.end < b.start


(just read the statement and you should see why there is no overlap in that
case)


The is overlap in the contrary. Negate the proposition (Use DeMorgan's
Law):


a.start <= b.end AND a.end >= b.start


So, you probably need two reference to your table:

============================
SELECT a.*, b.*
FROM myTable As a INNER JOIN myTable As b
ON a.start <= b.end AND a.end >= b.start
AND a.dateTime < b.dateTime
============================

The last condition is added since if "a" overlaps "b", then, also, ""b"
overlaps "a" and the database would list the fact twice. The last condition
brakes that symmetry (and using < instead of <= also breaks the
reflexivity, since "a" overlaps "a" ).



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

If you need to find out this info starting now, then I would change the
TimeBegin and TimeEnd time fields to include the complete date and time -
that way you can just substract the end from the beginning time.

I am fairly new to Access, but I would write several VB functions. The
first one would get the records needed as a recordset using a query ordered
by the starting date/time. Then set up your code to test the first record
with the second record, then the third, etc. The testing would be done in
the second function, which could return just whether it overlapped or not
(true or false) and increment an intreger.
Then take the second record and test it with the third, then fourth, etc.
Do this until you only test the next-to-last record with the last.

I have to leave for work in just a few minutes or I would do some of the
coding for you in case you don't know VB.

John H W