Finding overlapping period

[JoZ via AccessMonster.com]

I have two tables structured as the following:
t1:mbr, start_dt, end_dt
t2: mbr, group

Due to file load problem same members now see overlapping dates as:
mbr1, group1, 1/1/2008-12/31/2008
mbr1, group2, 4/1/2008-6/30/2008
mbr2, group1, 1/1/2008-null
mbr2, group2, 4/1/2008-6/30/2008

Overlapping always happens to members with multiple groups. How can I catch
members with such overlapping period?

Thanks for your help!

 

[Allen Browne]

2 things overlap if:
A starts before B ends, AND
B starts before A ends.

So, create a query that uses 2 copies of your table, joined by mbr. If the
table is called tblDate, Access will alias the 2nd one as tblDate_1. In the
critera row under tblDate.Start_dt, enter:
< tblDate_1.end_dt
In the criteria row under tblDate_1.End_dt enter:
< tblDate.end_dt

This will give you every record twice. You may want to add other criteria to
eliminate that.

If the logic above doesn't make sense, draw 2 timelines representing the
events, e.g.:
A ---------------------
B --------
You'll see it copes with them all.

 

[JoZ via AccessMonster.com]

Thank you so much for your help. It worked. Just one more element: A lot of
times the end date is null so I cannot do <end_date. What should I do? For
example, the following is considered as overlapping:

1/1/2008-Null
4/1/2008-Null

Thanks in advance!

2 things overlap if:
A starts before B ends, AND
B starts before A ends.

So, create a query that uses 2 copies of your table, joined by mbr. If the
table is called tblDate, Access will alias the 2nd one as tblDate_1. In the
critera row under tblDate.Start_dt, enter:
< tblDate_1.end_dt
In the criteria row under tblDate_1.End_dt enter:
< tblDate.end_dt

This will give you every record twice. You may want to add other criteria to
eliminate that.

If the logic above doesn't make sense, draw 2 timelines representing the
events, e.g.:
A ---------------------
B --------
You'll see it copes with them all.

I have two tables structured as the following:
t1:mbr, start_dt, end_dt

[quoted text clipped - 11 lines]

Thanks for your help!

 

[Allen Browne]

Perhaps you can reverse the logic, and use criteria on the end date of:
Is Null OR >= tblDate_1.start_dt

It can get messy. If you need to identify all potential overlaps where the
end dates are unknown, this might help:
Clashing Events/Appointments
at:
http://allenbrowne.com/appevent.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you so much for your help. It worked. Just one more element: A lot
of
times the end date is null so I cannot do <end_date. What should I do? For
example, the following is considered as overlapping:

1/1/2008-Null
4/1/2008-Null

Thanks in advance!

2 things overlap if:
A starts before B ends, AND
B starts before A ends.

So, create a query that uses 2 copies of your table, joined by mbr. If the
table is called tblDate, Access will alias the 2nd one as tblDate_1. In
the
critera row under tblDate.Start_dt, enter:
< tblDate_1.end_dt
In the criteria row under tblDate_1.End_dt enter:
< tblDate.end_dt

This will give you every record twice. You may want to add other criteria
to
eliminate that.

If the logic above doesn't make sense, draw 2 timelines representing the
events, e.g.:
A ---------------------
B --------
You'll see it copes with them all.

I have two tables structured as the following:
t1:mbr, start_dt, end_dt

[quoted text clipped - 11 lines]

 

[JoZ via AccessMonster.com]

Thank you for the suggestion. I'll explore it.

Perhaps you can reverse the logic, and use criteria on the end date of:
Is Null OR >= tblDate_1.start_dt

It can get messy. If you need to identify all potential overlaps where the
end dates are unknown, this might help:
Clashing Events/Appointments
at:
http://allenbrowne.com/appevent.html

Thank you so much for your help. It worked. Just one more element: A lot
of

[quoted text clipped - 31 lines]

t1:mbr, start_dt, end_dt
[quoted text clipped - 11 lines]