Date conflicts within range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am trying to set up a database to record work sites on roads.
I want to be able to have a warning come up if, when a new record is
entered, it conflicts (location and timing) with another site.
Getting the location conflicts is easy, as a query can be run to filter out
those sites with the same location.

In (SELECT [SH] FROM [Main_table] As Tmp GROUP BY [SH],[RS] HAVING
Count(*)>1 And [RS] = [Main_table].[RS])

However, I would like to be able to have the database tell me, if there IS a
location conflict, will the start date be within the date ranges of other
work sites at the same location. Each record has a start date and end date
field.

This is where I get stuck, how can I get Access to check if one record's
"start date" is between the "start date" and "end date" of all the other
records with conflicting locations?

Any ideas? Or referrals to an existing solution?

Cheers
 
Hi,



Interval [starting, ending] does not overlap at all interval [low, high]
if

low > ending OR high < starting


the two intervals overlap somehow in the negation (De Morgan's Law):


low < ending AND high > starting



So,


SELECT a.ID, EXISTS( SELECT *
FROM myTable As b
WHERE b.low < a.low AND b.high > a.starting
AND b.ID <> a.ID )
FROM myTable As a




should return, for each event ID, true or false if there is another event
overlapping it, in time.



Hoping it may help
Vanderghast, Access MVP
 
Thanks Michel, I'll give that a go.

Cheers
Cara

Michel Walsh said:
Hi,



Interval [starting, ending] does not overlap at all interval [low, high]
if

low > ending OR high < starting


the two intervals overlap somehow in the negation (De Morgan's Law):


low < ending AND high > starting



So,


SELECT a.ID, EXISTS( SELECT *
FROM myTable As b
WHERE b.low < a.low AND b.high > a.starting
AND b.ID <> a.ID )
FROM myTable As a




should return, for each event ID, true or false if there is another event
overlapping it, in time.



Hoping it may help
Vanderghast, Access MVP


Cara said:
Hi,

I am trying to set up a database to record work sites on roads.
I want to be able to have a warning come up if, when a new record is
entered, it conflicts (location and timing) with another site.
Getting the location conflicts is easy, as a query can be run to filter
out
those sites with the same location.

In (SELECT [SH] FROM [Main_table] As Tmp GROUP BY [SH],[RS] HAVING
Count(*)>1 And [RS] = [Main_table].[RS])

However, I would like to be able to have the database tell me, if there IS
a
location conflict, will the start date be within the date ranges of other
work sites at the same location. Each record has a start date and end date
field.

This is where I get stuck, how can I get Access to check if one record's
"start date" is between the "start date" and "end date" of all the other
records with conflicting locations?

Any ideas? Or referrals to an existing solution?

Cheers
Click to expand...
Click to expand...
 
Back
Top