番号が同じで指定日より小さい最新の日付

質問

access2002

部署、社員番号、社員の入社日、現部署への配属日のテーブルがあります。
busho,staffID,nyushaDate,haizokuDate
00001,1010100,2000/01/01,2000/01/01
00001,1010101,2000/04/01,2006/04/01
00001,1010102,2006/04/01,2007/04/10
00002,1010103,2000/04/01,2000/04/01
00002,1010104,2000/04/01,2006/04/01
00002,1010105,2006/04/01,2006/04/08
00003,1010106,2000/01/01,2000/01/01
00003,1010107,2006/01/01,2007/04/01
00003,1010108,2007/04/01,2007/10/01

このテーブルから指定した日付以前の入社日で各部署の最新の配属者を抽出したいと思っています。
例えば、2006年1月1日で検索すると
00001,1010101,2000/04/01,2006/04/01
00002,1010104,2000/04/01,2006/04/01
00003,1010107,2006/01/01,2007/04/01
という結果を望んでいます。

まず以下のようなクエリを作成しました。
SELECT T.busho, T.staffID, T.nyushaDate, Max(T.haizokuDate) AS haizokuDateの最大
FROM T
GROUP BY T.busho, T.staffID, T.nyushaDate
HAVING (((T.nyushaDate)<=[検索日?]));
しかし結果は、
00001,1010100,2000/01/01,2000/01/01
00001,1010101,2000/04/01,2006/04/01
00002,1010103,2000/04/01,2000/04/01
00002,1010104,2000/04/01,2006/04/01
00003,1010106,2000/01/01,2000/01/01
00003,1010107,2006/01/01,2007/04/01
となり配属最新日が抽出できませんでした。

次に過去ログをいろいろ調べて、以下のようなクエリを作成しました。
SELECT T.busho, T.staffID, T.nyushaDate, T.haizokuDate
FROM T
WHERE (((T.nyushaDate)<=[検索日?]) AND ((T.haizokuDate)=(SELECT MAX(haizokuDate) FROM T AS B WHERE T.busho=B.busho)));
しかし今度は、何も表示されませんでした。

何が間違っているのでしょうか。
どのようにすれば望む結果が得られるのでしょうか。
よろしくお願いします。

回答No.6

chukenkenkou

質問への直接の回答ではないですが、autyさんが説明しようとしているのは、SQL99（？）で標準SQLにも入った「行値構成子(row value constructor)」です。行値式と呼ばれる場合もあります。
主要なRDBMSでは実装されていますが、SQL Serverでは未実装であり、ACCESSでも未実装だと思います。

回答No.5

auty

数学でいうと
(x1,y1) = (x2,y2)
というようなもので、SQLの場合の
複数行副問い合わせ
に対応します。今回は、
select busho,max(haizokuDate)
となっている部分が2次元（2列の事です）となっています。
またイコールでなく
(x1,y1) in ( (x2,y2),(x1,y1),(x3,y3) )
の形をとっています。
この方法は、単にグループごとのmaxを求めるだけでなく（このままだとそのときのbusho情報が消えてしまいます。）、そのbusho情報をペアにして覚えておくことが出来ます。
Oracleの場合は、できるのですが。

お礼

ネットで調べて見ましたが、よく分かりませんでした。
私にはレベルが高すぎるようです。
お忙しいところ、ありがとうございました。

回答No.4

noname#140971

T:

busho___staffID_____nyushaDate___haizokuDate
______1___1010100___2000/01/01__2000/01/01
______1___1010101___2000/04/01__2006/04/01
______1___1010102___2000/04/01__2007/04/10
______2___1010103___2000/04/01__2000/04/01
______2___1010104___2000/04/01__2006/04/01
______2___1010105___2006/04/01__2006/04/08
______3___1010106___2000/01/01__2000/01/01
______3___1010107___2006/01/01__2007/04/01
______3___1010108___2007/04/01__2007/10/01

＞指定した日付以前の入社日で
＞各部署の最新の配属者を抽出

つまり、2006年1月1日以前の[busho]毎の最新の[haizokuDate]に該当するレコードを抜き出したいということ。
ならば、そのまま　SQL文で表現すれば・・・。

SELECT *　FROM T
WHERE haizokuDate=DBLookup("Max(haizokuDate)",
　　　　　　　　　　　　　　　　　　　　　"T",
　　　　　　　　　　　　　　　　　　　　　"nyushaDate<=#2006/01/01# AND busho=" & busho);

busho___staffID_____nyushaDate____haizokuDate
______1___1010102___2000/04/01___2007/04/10
______2___1010104___2000/04/01___2006/04/01
______3___1010107___2006/01/01___2007/04/01

ウヌヌ！busho=1 の該当レコードが違った。

Access は関数を組み込めるので単一Select文の体裁でも書けます。
DBLookup関数は、結局は SQL文を生成していますので、結局はNo１さんの回答と同じですが・・・。
メリットは、クエリを簡略化できるということです。

Public Function DBLookup(ByVal strField As String, _
　　　　　　　　　　　　 ByVal strTable As String, _
　　　　　　　　　　　　 Optional ByVal strWhere As String = "", _
　　　　　　　　　　　　 Optional ByVal ReturnValue = "") As Variant
On Error GoTo Err_DBLookup
　　Dim DataValue
　　Dim strQuerySQL As String
　　Dim rst　　　　 As ADODB.Recordset

　　Set rst = New ADODB.Recordset
　　strQuerySQL = "SELECT " & strField & " FROM " & strTable
　　If Len(strWhere) > 0 Then
　　　　strQuerySQL = strQuerySQL & " WHERE " & strWhere
　　End If
　　With rst
　　　　.Open strQuerySQL, _
　　　　　　　CurrentProject.Connection, _
　　　　　　　adOpenStatic, _
　　　　　　　adLockReadOnly
　　　　If Not .BOF Then
　　　　　　.MoveFirst
　　　　　　DataValue = .Fields(0)
　　　　End If
　　End With
Exit_DBLookup:
On Error Resume Next
　　rst.Close
　　Set rst = Nothing
　　DBLookup = IIf(Len(DataValue & ""), DataValue, ReturnValue)
　　Exit Function
Err_DBLookup:
　　MsgBox "SELECT 文の実行時にエラーが発生しました。(DBLookup)" & Chr$(13) & Chr$(13) & _
　　　　　 "・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　 "・SQL Text=" & strQuerySQL, _
　　　　　 vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_DBLookup
End Function

お礼

ちょっと難しそうですが、チャレンジしてみます。
ありがとうございました。

回答No.3

nora1962

SELECT T1.*
FROM T T1
WHERE T1.nyushaDate<=[検索日?]
AND NOT EXISTS
( SELECT 1 FROM T T2
WHERE T1.busho = T2.busho
AND T2.nyushaDate<=[検索日?]
AND T1.haizokuDate < T2.haizokuDate );

お礼

ようやくできました。
ありがとうございました。

回答No.2

auty

[検索日?]は、よく分かっていませんが、次の方法を試してみてください。

・　２次元の比較が行えるときは、

SELECT busho, staffID, nyushaDate, haizokuDate AS haizokuDateの最大
FROM T
where nyushaDate<=[検索日?] and (busho,haizokuDate) in (
select busho,max(haizokuDate) from T
GROUP BY busho
where nyushaDate<=[検索日?]);

・　２次元の比較が使えないときは、
SELECT busho, staffID, nyushaDate, haizokuDate AS haizokuDateの最大
FROM T T1
where nyushaDate<=[検索日?] and haizokuDate=(
select max(T2.haizokuDate) from T T2
where T2.nyushaDate<=[検索日?] and T2.busho=T1.busho);

お礼

済みません。
『２次元の比較』というものが分かりません。
どのようなものなのでしょうか。

回答No.1

CHRONOS_0

SELECT T.busho, T.staffID, T.nyushaDate, T.haizokuDate
FROM T
WHERE T.haizokuDate=(SELECT MAX(haizokuDate) FROM T AS B WHERE T.busho=B.busho and B.nyushaDate<=[検索日?]);

お礼

できました。
ありがとうございました。