Isolating Overlapping Date Ranges on Multiple Records

[StephenB]

Hello.
Scenario: Customers have been enrolled with a company for different service packages on and off over a period of years.
Problem: The customer table contains bad data in that the service periods are not accurate. No service period should overlap.
Goal: I need to identify records with overlapping date ranges in order to research and correct them.

Please see attached database (Access 2007)

(1) tbl_main has the following fields:

(a) RecordID (Not part of data.)
(b) CustomerID Text
(c) Name Text
(d) Start Date Text YYYYMMDD. So 1/1/2011 = "20110101".
(e) End Date Text YYYYMMDD.
(e) Comment (For informational purposes only. Not part of data.)
(f) Status (For informational purposes only. Not part of data.)​

(2) The table contains 4 customers,
(3) Each customer has 3 records for each service period the customer is with the company;
(4) for a total of 12 records.
(3) Each record is either termed, or active:

(a) A termed record will have an end date.
(b) An active (or open) record will have all zeroes in the end date.​

(6) I don't mind converting the date fields to true date fields if helps, but would rather not if we can help it.
(8) We are only looking to identify overlapping date ranges at the name level only. So we don't care if John has an overlapping date range with Michael.
(9) Some of a member's records may overlap, but not all.
(10) I need to identify overlapping records (at the member level)
(11) I have included comments and statuses for each record for informational purposes only. They are not available on the original data.
(12) The status column is my goal: To determine whether or not a record's date range overlaps with another record for the same member.

Thanks in advance for any insight.

 

[spikepl]

PBaldy has an algorithm for overlapping ranges here : http://www.baldyweb.com/OverLap.htm

Otherwise: be very specific as to what you ask for. It is difficult to determine from your post with which specific issue you need help.

 

[StephenB]

Thank you spikepl. I need to determine which records have overlapping date ranges (for each customer only.) Checking out your link now.

 

[StephenB]

Checked out the link provided by spikepl. I see how it could be helpful in other scenarios where all records must be compared against each for "clashes". However, my scenario only requires that records for one person be compared.

 

[StephenB]

Bumping, perhaps someone can take another look. Thanks.

 

[MSAccessRookie]

Checked out the link provided by spikepl. I see how it could be helpful in other scenarios where all records must be compared against each for "clashes". However, my scenario only requires that records for one person be compared.

Stephen8,

In SQL, any set of data can usually be cut down to a sub-group by using either the proper WHERE Statement, or a HAVING Statement (if the set of data also contains a GROUP BY Statement). Is there no way for you to do something like that?

 

[vbaInet]

Why don't you show us a spreadsheet of:

1. your current set of records
2. the from and to date criteria
3. the results you expect to get