Access2007で必要なレコードを抽出したい

質問

Access2007です。
よろしくお願いします。

下記のようなレコードが複数あります。

予定日　　　　コード　　　　氏名　　　　開始時間　　　　終了時間
2013/03/01　　1111　　　　AAA　　　　09:00　　　　　　 10:00
2013/03/01　　1111　　　　AAA　　　　13:00　　　　　　 14:00
2013/03/01　　1111　　　　AAA　　　　08:40　　　　　　 09:00

上記以外にも複数日、複数人います。時間は08:40から18:00までの間で存在します。
ほしい結果は、2013/03/01のAAAの空き時間です。
何時から何時まであいていて、その時間は何分です、というような答えが欲しいのです。
その際、12:00から13:00の時間は除きます。

基本VBAを使用して作成しています。
何らかのヒントでもいただければと思います。よろしくお願いします。

回答No.2

nicotinism · ベストアンサー

はい。そのつもりで回答しました。
結果や如何に？
※時間が重複している場合は考えてません
2013/03/01　　1111　　　　AAA　　　　09:00　　　　　　 10:00
↓AAAさんが掛け持ちで何かやっている場合など
2013/03/01　　1111　　　　AAA　　　　09:00　　　　　　 10:00
2013/03/01　　1111　　　　AAA　　　　09:00　　　　　　 09:30
その他、未入力の場合など。

１２～１３時の処理ですが
前回回答では、8：40 ～18：00で考えてましたが
8:40 ～ 12:00 と 13:00 ～ 18:00 に分けて
Where 終了時間 >= #12:00#
Where 開始時間 <= #13:00#
の二つのレコードセットでそれぞれ処理すれば良さそうです。
ただ、昼食時間をまたがっている場合があると・・・没。
2013/03/01　　1111　　　　AAA　　　　11:00　　　　　　 13:30

もっと洗練された考え方が有るような気がします。

お礼

ご回答ありがとうございます。
またご連絡が遅くなり申し訳ありません。
同時進行で他のものも作成しておりましたもので・・・。

おかげさまで空き時間が取得できましたので、まずは一つ解決しました。
昼食時間もアドバイスの感じで二つにわければよさそうです。
ただ、他にも条件がありますのでクリアしなければいけませんが、
ご教示いただいた内容でだいぶ感じがつかめましたので、
なんとかなりそうです。
本当に助かりました。ありがとうございました。

また何かありましたらよろしくお願いします。

回答No.3

30246kiku

変てこな考えかも（処理性能は考えてませんので・・・）

除外する・・・これは結構厄介なものと思います。
タイムスライスして、ある・ない・・・で結果を得ても良いのかも

フィールド「時刻」（日付/時刻型：主キー）のみのテーブル「T時刻」を作り、
0:00 ～ 23:59 までのデータを入れておきます。（60 * 24 = 1440 レコード）

この「T時刻」を基準に、対象者の予定日と開始時間、終了時間を結び付けて、
その結果、結びついていない時刻をグルグルとみて、空きの開始、終了を処理すれば・・・

元テーブル名を★★と仮定すると

SELECT Q1.時刻
FROM T時刻 AS Q1 LEFT JOIN
(SELECT * FROM ★★ WHERE 予定日=#2013/3/1# AND 氏名='AAA') AS Q2
ON (Q1.時刻 Between Q2.開始時間 AND Q2.終了時間)
WHERE
(Q2.氏名 Is Null) AND
((Q1.時刻 Between #8:40# AND #12:00#) OR (Q1.時刻 Between #13:00# AND #18:00#));

の様な感じで・・・・
得られる結果は、10:01 ～ 12:00 の 120レコードと 14:01 ～ 18:00 の240レコード
全体で 360レコードになるので、360分空いている事がわかります。
ただ、注意しないといけないのは、時刻の境目・・・・

例えば、上記の空きで 15:00 ～ 15:30 を予定に入れたら、
14:01 ～ 14:59、15:31 ～ 18:00 に分かれます。
ということは、レコード数＝分　ではなくなる？

そこで、開始と終了の時刻が重ならないように、終了の方を１分前にします。

SELECT Q1.時刻
FROM T時刻 AS Q1 LEFT JOIN
(SELECT * FROM ★★ WHERE 予定日=#2013/3/1# AND 氏名='AAA') AS Q2
ON (Q1.時刻 Between Q2.開始時間 AND DateAdd("n",-1,Q2.終了時間))
WHERE
(Q2.氏名 Is Null) AND
((Q1.時刻 Between #8:40# AND #11:59#) OR (Q1.時刻 Between #13:00# AND #17:59#));

これで得られる結果は、10:00 ～ 11:59 の 120レコードと 14:00 ～ 17:59 の240レコード

上記の空きで 15:00 ～ 15:30 を予定に入れたら、
14:00 ～ 14:59、15:30 ～ 17:59 に分かれます。
（レコード数＝分　でしょうか）

(Q1.時刻 Between #8:40# AND #11:59#) は、
(Q1.時刻 >= #8:40# AND Q1.時刻 < #12:00#)　の方が考えやすい/指定しやすいかも・・・

上記を指定して Recordset を得て、グルグル回して、時刻差が 1 分でなかったら
開始の時刻はそのまま・・・
終了の方は1分加算・・・・
で、求まっていくと思います。
空きの時間帯が複数あっても、グルグル回る時の処理を考えれば良いと思います。
また、どんな時間帯で登録されていようが、求まったものを処理するだけです。

※
上記では 1分間隔で「T時刻」内を作成していましたが、入力が 10分単位とかなら、
登録しておく「時刻」も 10分間隔で良いと思います。
グルグル回る時には、時刻差が 10分でなかったら・・・・とか

※ 上記記述をクエリとして作成した場合
保存・閉じた後、再度SQLビューで覗くと

ON (Q1.時刻 Between Q2.開始時間 AND DateAdd("n",-1,Q2.終了時間))

部分は

ON Q1.時刻 Between Q2.開始時間 AND DateAdd("n",-1,Q2.終了時間)

の様に ( ) が外れます。
編集等しなければ何も影響ありませんが、編集した後保存しようとするとエラーになります。
その時には、消えた ( ) を付け直してください。
（何故なのか・・・・わかりません）

※ 冒頭でも記述しましたが、処理性能は考えてませんので・・・

お礼

ご回答ありがとうございます。

タイムテーブルは考えてなかったですね～。
予定を入力するフォームは5分刻みなので、その間隔で作成しておく
ということですね。
ただご指摘の通り、処理能力がどうかというところですね。
空き時間を求めて予定を登録するのですが、実はそこに来るまでにも
少しぐるぐる回る処理をしていまして、使用者に遅いと思われないような
くらいならOKですが、ちょっとやってみないと、ですね。
いろいろとたくさん考えていただきましてありがとうございます。
次への経路も見えて助かりました。
ちょくちょく息詰まりますので、また何かありましたらよろしくお願いします。

回答No.1

nicotinism

途中までですが・・・
昼食時間を抜く処理が・・・
例示された場合だけです・・・。

Sub b()
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Const tS As Date = #8:40:00 AM#
Const tE As Date = #6:00:00 PM#
Dim tT As Date
Dim Ary() As Variant
Dim i As Integer

Set DB = CurrentDb
Set RS = DB.OpenRecordset("SELECT 予定日, [コード], 氏名, 開始時間, 終了時間 FROM New1 ORDER BY 開始時間", dbOpenSnapshot)

tT = tS
Do Until RS.EOF
If tT < RS!開始時間 Then
ReDim Preserve Ary(i)
Ary(i) = tT & " - " & RS!開始時間 _
& " 空き時間=" & CDate(tT - RS!開始時間)
i = i + 1
End If

tT = RS!終了時間
RS.MoveNext
Loop

If tT < tE Then
ReDim Preserve Ary(i)
Ary(i) = tT & " - " & tE _
& " 空き時間=" & CDate(tT - tE)
i = i + 1
End If

For i = 0 To UBound(Ary)
Debug.Print Ary(i)
Next
End Sub

お礼

nicotinism　様

ご回答ありがとうございます。
また返信がおそくなりましてすいません。

ご教示いただいた内容ですと、一番最初の開始時間と08:40の差、
次に終了時間と次の予定の開始時間の差、、、と続いて、そして最後の予定の終了時間と18:00の差を
求めればいい。
これに昼食時間を除く処理をかけばいい。
というふうに解釈しましたが、よろしいでしょうか？