Overlapping time in Access

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?
--
Robert C. Melton, Stockton, California

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


On Fri, 10 Sep 2004 15:11:05 -0700, "RobertMelton,MOUS"
wrote:

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?

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

"Tom Ellison" wrote in message
...
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


On Fri, 10 Sep 2004 15:11:05 -0700, "RobertMelton,MOUS"
wrote:

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?