Over Lap Time Period Question

[SageOne]

I have a table of over 100,00 membership health coverage records. Each
record displays a start date of membership, an end date of membership, and a
name.

Start Date End Date Name
Plan
11/05/2006 12/20/2007 Joe Smith Plan A
12/21/2007 12/21/2008 Joe Smith Plan B

There can only be one Plan per person per time period. But because of human
data entry error, some people in this list have overlapping coverage and show
as having two plans per the same time period.

Example

Start Date End Date Name
Plan
11/05/2006 12/20/2007 Joe Smith
Plan A
10/20/2007 12/21/2008 Joe Smith
Plan B

As you can see above, Joe Smith's Plan coverage over laps during the months
of Oct, Nov, and Dec.

Is there a way to pull from the 100,000 records only the occurrences where
coverage is over lapping?

 

[Allen Browne]

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

Steps:

1. Create a query using 2 copies of this table. (If the table is called
tblMembership, Access will alias the 2nd one as tblMembership_1.)

2. Join the tables on MemberID (or name.)

3. In the Criteria row under tblMembership_1.Plan, enter:
<> tblMembership.Plan

4. In the criteria row under tblMembership_1.[Start Date], enter:
< tblMembership.[End Date]

5. In the criteria row under tblMembership.[Start Date], enter:
< tblMembership_1.[End Date]

Overlaps will show twice (i.e. the 2 records that overlap will both show.)

 

[James A. Fortune]

I have a table of over 100,00 membership health coverage records. Each
record displays a start date of membership, an end date of membership, and a
name.

Start Date End Date Name
Plan
11/05/2006 12/20/2007 Joe Smith Plan A
12/21/2007 12/21/2008 Joe Smith Plan B

There can only be one Plan per person per time period. But because of human
data entry error, some people in this list have overlapping coverage and show
as having two plans per the same time period.

Example

Start Date End Date Name
Plan
11/05/2006 12/20/2007 Joe Smith
Plan A
10/20/2007 12/21/2008 Joe Smith
Plan B

As you can see above, Joe Smith's Plan coverage over laps during the months
of Oct, Nov, and Dec.

Is there a way to pull from the 100,000 records only the occurrences where
coverage is over lapping?

SageOne,

See:

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/ed0d6ab1c02179a9

With 100,000 records it's likely that you have more than one insured
named Joe Smith so your schema is possibly suspect. Also, with 100,000
records, the kind of subquery typically used for those situations
becomes quite slow even with proper indexing (here, two dates come from
the main query and two from the subquery). Even using the fact that Joe
Smith is only compared with Joe Smith and creating a self-join on
InsuredID (ignoring records/rows where the CoverageID values are the
same) to get all the date combinations required will be slow. Your only
solution with reasonable speed is likely to involve some VBA code
constructed in such a way that a minimum number of passes through the
table, preferably only one, are made. Another alternative is to break
the table into ten tables of about 10,000 records/rows each where each
table contains all the coverages for a subset of InsuredID's, then run
your query on each table. A VBA solution that pulls a given InsuredID's
coverages into a recordset inside a loop might be quick enough. If you
do that, be sure to get your list of possible InsuredID's from
tblInsured rather than from DISTINCT InsuredID's from tblCoverages.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

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

Steps:

1. Create a query using 2 copies of this table. (If the table is called
tblMembership, Access will alias the 2nd one as tblMembership_1.)

2. Join the tables on MemberID (or name.)

3. In the Criteria row under tblMembership_1.Plan, enter:
<> tblMembership.Plan

4. In the criteria row under tblMembership_1.[Start Date], enter:
< tblMembership.[End Date]

5. In the criteria row under tblMembership.[Start Date], enter:
< tblMembership_1.[End Date]

Overlaps will show twice (i.e. the 2 records that overlap will both show.)

I just tried Allen's suggestion. It is reasonably fast even with
100,000+ records.

James A. Fortune
(e-mail address removed)