Overlapping Records

[Les]

Hello

Could someone please help me with a query to filter out records in a table
that overlap.

I have a table with 8 fields and approx 40,000 records.
Fields 1 and 2 relate to a position, fields 3 and 4 are 'from' and 'to'
measurements in yards and will be where the overlap exists, field 5 is a
date. The other 3 fields are not needed for the query.


What I need the query to do is return the records where fields1,2 and 5
match and fields 3 and 4 overlap.

Thanks

 

[Gary Walter]

Could someone please help me with a query to filter out records in a table
that overlap.

I have a table with 8 fields and approx 40,000 records.
Fields 1 and 2 relate to a position, fields 3 and 4 are 'from' and 'to'
measurements in yards and will be where the overlap exists, field 5 is a
date. The other 3 fields are not needed for the query.


What I need the query to do is return the records where fields1,2 and 5
match and fields 3 and 4 overlap.

Hi Les,

Just join table to second instance of itself
on fields 1,2 and 5, then test overlap

SELECT *
FROM
yurtable As a
INNER JOIN
yurtable As b
ON
a.f1=b.f1
AND
a.f2=b.f2
AND
a.f5=b.f5
WHERE
a.[from] <= b.[to]
AND
a.[to] >= b.[from];


i.e.,

WHERE
a.f3 <= b.f4
AND
a.f4 >= b.f3;

http://allenbrowne.com/appevent.html

good luck,

gary

 

[Les]

Hi Gary

Firstly thanks for the prompt response and your help.

This is very, very close to what I was after. The query runs fine but what I
find I get returned are only the instances where a. from = b. to.
I don't get any records returned where a's start point is greater than b's
finish and vice versa.

Could someone please help me with a query to filter out records in a table
that overlap.

I have a table with 8 fields and approx 40,000 records.
Fields 1 and 2 relate to a position, fields 3 and 4 are 'from' and 'to'
measurements in yards and will be where the overlap exists, field 5 is a
date. The other 3 fields are not needed for the query.


What I need the query to do is return the records where fields1,2 and 5
match and fields 3 and 4 overlap.

Hi Les,

Just join table to second instance of itself
on fields 1,2 and 5, then test overlap

SELECT *
FROM
yurtable As a
INNER JOIN
yurtable As b
ON
a.f1=b.f1
AND
a.f2=b.f2
AND
a.f5=b.f5
WHERE
a.[from] <= b.[to]
AND
a.[to] >= b.[from];


i.e.,

WHERE
a.f3 <= b.f4
AND
a.f4 >= b.f3;

http://allenbrowne.com/appevent.html

good luck,

gary

 

[Les]

Gary

Just to give you a an example:

On 04/04/2007 I have 3 records.

from 19263 to 19610
from 19472 to 19658
from 19658 to 21340

The query picks out that the start of 19658 is equal to the record on the
same date where the end is 19658 but it doesn't pick out that the record
ending in 19610 would overlap the record starting 19472.

Hope this helps.

Regards

Les.

Hi Gary

Firstly thanks for the prompt response and your help.

This is very, very close to what I was after. The query runs fine but what I
find I get returned are only the instances where a. from = b. to.
I don't get any records returned where a's start point is greater than b's
finish and vice versa.

Could someone please help me with a query to filter out records in a table
that overlap.

I have a table with 8 fields and approx 40,000 records.
Fields 1 and 2 relate to a position, fields 3 and 4 are 'from' and 'to'
measurements in yards and will be where the overlap exists, field 5 is a
date. The other 3 fields are not needed for the query.


What I need the query to do is return the records where fields1,2 and 5
match and fields 3 and 4 overlap.

Hi Les,

Just join table to second instance of itself
on fields 1,2 and 5, then test overlap

SELECT *
FROM
yurtable As a
INNER JOIN
yurtable As b
ON
a.f1=b.f1
AND
a.f2=b.f2
AND
a.f5=b.f5
WHERE
a.[from] <= b.[to]
AND
a.[to] >= b.[from];


i.e.,

WHERE
a.f3 <= b.f4
AND
a.f4 >= b.f3;

http://allenbrowne.com/appevent.html

good luck,

gary

 

[John Spencer]

I suggest you post the SQL of the query you are using.

The example that Gary posted should work.

Is it possible that you forgot to use <= (Less than or equal) or >= (greater
than or equal) in the criteria.

For instance, I would expect the results you got if you had
WHERE a.f3 <= b.f4 AND a.f4 = b.f3
instead of
WHERE a.f3 <= b.f4 AND a.f4 >= b.f3

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Gary

Just to give you a an example:

On 04/04/2007 I have 3 records.

from 19263 to 19610
from 19472 to 19658
from 19658 to 21340

The query picks out that the start of 19658 is equal to the record on the
same date where the end is 19658 but it doesn't pick out that the record
ending in 19610 would overlap the record starting 19472.

Hope this helps.

Regards

Les.

Hi Gary

Firstly thanks for the prompt response and your help.

This is very, very close to what I was after. The query runs fine but
what I
find I get returned are only the instances where a. from = b. to.
I don't get any records returned where a's start point is greater than
b's
finish and vice versa.

:

Could someone please help me with a query to filter out records in a
table
that overlap.

I have a table with 8 fields and approx 40,000 records.
Fields 1 and 2 relate to a position, fields 3 and 4 are 'from' and
'to'
measurements in yards and will be where the overlap exists, field 5
is a
date. The other 3 fields are not needed for the query.


What I need the query to do is return the records where fields1,2 and
5
match and fields 3 and 4 overlap.

Hi Les,

Just join table to second instance of itself
on fields 1,2 and 5, then test overlap

SELECT *
FROM
yurtable As a
INNER JOIN
yurtable As b
ON
a.f1=b.f1
AND
a.f2=b.f2
AND
a.f5=b.f5
WHERE
a.[from] <= b.[to]
AND
a.[to] >= b.[from];


i.e.,

WHERE
a.f3 <= b.f4
AND
a.f4 >= b.f3;

http://allenbrowne.com/appevent.html

good luck,

gary

 

[Gary Walter]

well...let's see

they do not overlap if

a.from < b.to
or
a.to > b.from

so they do overlap if

a.from >= b.to
and
a.to <= b.from

If Record1 provides start S1 and end E1,
Record2 provides start S2 and end E2,
the single expression


(S2 < E1 AND E2 > S1)


should reveal whether overlap or not


No overlap: S1 E1 S2 E2 (S2 <
E1 AND E2 > S1)


Record1 ____[_____]___________ 2 4 5 8 (5 < 4 AND 8 >
2 ) = False
Record2 ____________[______]__


Second starts during first:
Record1 ____[_____]___________ 2 4 3 6 (3 < 4 AND 6 >
2 ) = True
Record2 _______[_______]______


Second ends during first:
Record1 ____[_____]___________ 2 4 1 3 (1 < 4 AND 3 > 2 )
= True
Record2 _[______]_____________


Second entirely within first:
Record1 ____[_____]__________ 2 5 3 4 (3 < 5 AND 4 > 2 )
= True
Record2 ______[__]___________

Gary

Just to give you a an example:

On 04/04/2007 I have 3 records.

from 19263 to 19610
from 19472 to 19658
from 19658 to 21340

The query picks out that the start of 19658 is equal to the record on the
same date where the end is 19658 but it doesn't pick out that the record
ending in 19610 would overlap the record starting 19472.

Hope this helps.

Regards

Les.

Hi Gary

Firstly thanks for the prompt response and your help.

This is very, very close to what I was after. The query runs fine but
what I
find I get returned are only the instances where a. from = b. to.
I don't get any records returned where a's start point is greater than
b's
finish and vice versa.

:

Could someone please help me with a query to filter out records in a
table
that overlap.

I have a table with 8 fields and approx 40,000 records.
Fields 1 and 2 relate to a position, fields 3 and 4 are 'from' and
'to'
measurements in yards and will be where the overlap exists, field 5
is a
date. The other 3 fields are not needed for the query.


What I need the query to do is return the records where fields1,2 and
5
match and fields 3 and 4 overlap.

Hi Les,

Just join table to second instance of itself
on fields 1,2 and 5, then test overlap

SELECT *
FROM
yurtable As a
INNER JOIN
yurtable As b
ON
a.f1=b.f1
AND
a.f2=b.f2
AND
a.f5=b.f5
WHERE
a.[from] <= b.[to]
AND
a.[to] >= b.[from];


i.e.,

WHERE
a.f3 <= b.f4
AND
a.f4 >= b.f3;

http://allenbrowne.com/appevent.html

good luck,

gary