find duplicates in time and date records

[lateacher]

I have a schedule database.

I have date, start time and end time fields .

When I create new records I need to be able to find repetitions and overlapping records for I can't allow anyone to have more than one activity happening at the same time the same day.

The start time could be different and still be overlapping.

I tried using global fields and scripting but it didn't work out.

Anyone can help?

Thank you in advance for your cooperation

[LiveOak]

This isn't exactly simple. We have accomplished this by creating a calculation field that is a multikey, combining date and time slot. First decide what you minimum increment for scheduling is (i.e. every hour, half hour, 15 minutes, etc.). If you decide every minute, you are in for a lot of work. The trick is to take the date, start time, and end time and create a field as the key for a relationship. I'll use an example where the time interval is 1 hour. If an appointment was scheduled on June 20, 2001 from midnight to 0100 hrs the key might be:

6/20/2001-1

From midnight to 0400 hours, the key would be:

6/20/2001-1

6/20/2001-2

6/20/2001-3

From 1100 hrs to 1400 hrs, the key would be:

6/20/2001-12

6/20/2001-13

6/20/2001-14

This second multi-key will match the first key (6/20/2001-1) and can be used to show an appointment overlap using a Count() function based upon a a related field. It is probably easier to build this multi-key with a looping script, but it probably could be built as a calculated field using a case statement.

-bd