一定の「期間」を記録したレコードが多数ある場合に、その期間が重複していることをチェックし、抽出する方法についてです。
なお、ここでの重複とは「期間のうち一部でも重複している」という状態を指します。
例として右のようなテーブルを用るものとします。さまざまなイベントが記録されていて、「開始日」と「終了日」という列によりその期間が定められています。当然ですが終了日は開始日以降の日付となっています。
まず、実際にこれらの期間がどのように重なっているのかを整理してみると次のようになります。
例として2番目のイベント「LLサイズ婦人服バーゲン」に対し、期間が(一部でも)重複しているイベントを探してみると「ヘアケアまつり」「手作りzakka作品展」「北海道物産展」の3つがあることがわかります。
一方「ヘアケアまつり」からみて期間が重複しているイベントは「LLサイズ婦人服バーゲン」「北海道物産展」の2つです。
期間の重複判定について
判定の実例を挙げる前に、期間の重複の判定について整理してみます。
重複するパターンを列記するとかえってややこしくなりますので、先に重複しないパターンを示します。
それは下記の2パターンです。
つまり、2つのイベントA,Bが存在するとき「イベントAの開始日より先にイベントBが終了日を迎える または イベントAの終了日より後にイベントBの開始日を迎える」ことが「期間が重複しない」条件となります。さらに見やすく式の形で表せば
「イベントA開始日 > イベントB終了日 OR イベントA終了日 < イベントB開開始日」
となります。
反対に、この条件を満たさなければ期間は重複します。つまり、2つのイベントA,Bが存在するとき
「イベントA開始日 <= イベントB終了日 AND イベントA終了日 >= イベントB開開始日」
これが「期間が重複する」条件です。
なかなか難しいですが、ピンとこない場合はさまざまな重複例を挙げるなどして検討してみてください。
例1:各レコードに対し重複の有無を示す例
上記の内容を踏まえて、クエリで各レコード(イベント)に対し、重複の有無を示す値を表示してみます。
その例の1つとして、Dcount関数を用いて上記の条件を適用し、各イベントと期間が重複するイベントの数を表示してみます。
重複数: DCount("*","催事テーブル","催事コード<>" & [催事コード] & " AND 開始日<=#" & [終了日] & "# AND 終了日>=#" & [開始日] & "#")
「"催事コード<>" & [催事コード]」という部分がないと、自らのイベントを1つと数えてしまうので注意してください。
データシートビューです。
「LLサイズ婦人服バーゲン」に対し3つのイベントが重複していることが示されているなど、上記の図から読み取れる重複の状況と一致していることがわかります。
別の例としてサブクエリで重複をチェックし、重複の有無のみを表示してみます。
重複有無: IIf(Exists (SELECT 催事コード FROM 催事テーブル AS 催事テーブル_1 WHERE 催事テーブル.催事コード<>催事テーブル_1.催事コード AND 催事テーブル.開始日<=催事テーブル_1.終了日 AND 催事テーブル.終了日>=催事テーブル_1.開始日),"有","無")
データシートビューです。
例2:重複するレコードの組み合わせを示す例
別の例として、期間が重複するイベントの組み合わせをすべて求めるクエリを作成してみます。レコードが多い場合は、他のレコードと期間の重複があることがわかっても、どのイベントと重複しているのか特定するのが容易でありませんのでこちらの方が適当かと思います。
同一のテーブルを2つ置いて抽出する形になります。ちょっと複雑ですが注意してみてください。
ちなみに抽出条件の「>[催事テーブル].[催事コード]」の部分ですが、ここの不等号を「<>」にしてしまうと同じ組み合わせが2回ずつ出てしまいます。
データシートビューです。