Date Overlap Challenge

[John Baker]

HI;

I have a table where the user is entering data which shows scheduling for member of teams.
One problem we have encountered is that sometimes the inputter (is this a word) puts in
dates for an individual that overlap, creating all sorts of problems. One record is
entered for each scheduled event (each one is a day in length or more), so that an
individual will have multiple events on the table.

For the purposes of this question, assume there are 4 fields (there are really many more):

Associate
Activity
start date
end date
(start and end date can be the same).

Does anyone know of a write up on an easy way to detect these overlaps and produce a
report listing them?

Any pointers would be most helpful. I know i ran into something on the web last month that
appeared to do this, but have totally lost the reference to it.

Regards

John Baker

[Tom van Stiphout]

On Wed, 04 Feb 2004 11:43:52 GMT, John Baker <Ba******@Verizon.net>
wrote:

That report would be empty. Because you should perform the check when
the data is entered, and not allow overlapping dates.
You can run a number of queries to find the count of records that have
date ranges overlapping with the proposed start date, and similarly (a
parameter query comes to mind!) with the proposed end date. If the
count is > 0, you show a messagebox "No double entry!" and set Cancel
to True (BeforeUpdate event).

-Tom.

HI;

I have a table where the user is entering data which shows scheduling for member of teams.
One problem we have encountered is that sometimes the inputter (is this a word) puts in
dates for an individual that overlap, creating all sorts of problems. One record is
entered for each scheduled event (each one is a day in length or more), so that an
individual will have multiple events on the table.

For the purposes of this question, assume there are 4 fields (there are really many more):

Associate
Activity
start date
end date
(start and end date can be the same).

Does anyone know of a write up on an easy way to detect these overlaps and produce a
report listing them?

Any pointers would be most helpful. I know i ran into something on the web last month that
appeared to do this, but have totally lost the reference to it.

Regards

John Baker

[John Baker]

Tom:

The snag is that there are some situations where overlap is acceptable (I wont go into
details), and what we need is a human review of all duplicates, with identification of
those that are acceptable. Hence, I need to list all overlaps, and then have someone check
them out.

Any help much appreciated.

Regards

John Baker

Tom van Stiphout <to*****@no.spam.cox.net> wrote:

On Wed, 04 Feb 2004 11:43:52 GMT, John Baker <Ba******@Verizon.net>
wrote:

That report would be empty. Because you should perform the check when
the data is entered, and not allow overlapping dates.
You can run a number of queries to find the count of records that have
date ranges overlapping with the proposed start date, and similarly (a
parameter query comes to mind!) with the proposed end date. If the
count is > 0, you show a messagebox "No double entry!" and set Cancel
to True (BeforeUpdate event).

-Tom.

HI;

I have a table where the user is entering data which shows scheduling for member of teams.
One problem we have encountered is that sometimes the inputter (is this a word) puts in
dates for an individual that overlap, creating all sorts of problems. One record is
entered for each scheduled event (each one is a day in length or more), so that an
individual will have multiple events on the table.

For the purposes of this question, assume there are 4 fields (there are really many more):

Associate
Activity
start date
end date
(start and end date can be the same).

Does anyone know of a write up on an easy way to detect these overlaps and produce a
report listing them?

Any pointers would be most helpful. I know i ran into something on the web last month that
appeared to do this, but have totally lost the reference to it.

Regards

John Baker