Overlapping time in Access

R

RobertMelton,MOUS

Here's the situation, we have a school. Some students are part of a paid work
experience program in another part of the school. Student records are kept in
one database. When a student signs in for class, we record time in / time
out. When they are on property for work we also include time in / time out.
We DONT want anyone to clock in for both at the same time(get credit for 4
hours class during the same time as 4 hours work), but different people input
the class time and work time. Is there a macro or query that can make sure
that "start time" and "end time" for either work or class is not in between
that students "Start time" and "end time" that already exists on the same
date?
 
T

Tom Ellison

Dear Robert:

There's nothing obvious in Access to do this, but it can be done if
the database design is executed properly. The details of how to do
that in your setup I cannot provide without knowing about your designs
in some depth.

You'll need the math first. To check for an overlap, we can refer to
the data as follows:

A1 first record, date/time IN
B1 first record, date/time OUT
A2 second record, date/time IN
B2 second record, date/time OUT

There is an overlap if, and only if, A2 lies between A1 and B1 or if
B2 lies between A1 and B1. By "between" I mean

A2 >= A1 AND < B1
B2 > A1 and <= B2

By making this test for every combination in your table, it will also
find the converse, where A1 lies between A2 and B2 or B1 lies between
A2 and B2. This comparison need only be made within the rows for the
same student (obviously).

On that basis, a query can be built dependent on how your information
is constructed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
B

Brian Camire

If you can assume that the "B" (out) time is not before the "A" (in) time,
then the conditions for overlap boil down to:

A1 < B2 AND B1 > A2
 
Top