こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

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

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)));
しかし今度は、何も表示されませんでした。

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

投稿日時 - 2008-03-28 00:03:20

QNo.3900760

すぐに回答ほしいです

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(6)

ANo.6

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

投稿日時 - 2008-03-29 17:40:07

ANo.5

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

投稿日時 - 2008-03-28 12:57:06

お礼

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

投稿日時 - 2008-03-29 16:58:06

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文を生成していますので、結局はNo1さんの回答と同じですが・・・。
メリットは、クエリを簡略化できるということです。

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

投稿日時 - 2008-03-28 10:17:05

お礼

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

投稿日時 - 2008-03-29 16:50:53

ANo.3

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 );

投稿日時 - 2008-03-28 03:01:26

お礼

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

投稿日時 - 2008-03-29 16:48:11

ANo.2

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

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

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<=[検索日?]);


・ 2次元の比較が使えないときは、
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);

投稿日時 - 2008-03-28 01:25:14

お礼

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

投稿日時 - 2008-03-28 11:40:24

ANo.1

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<=[検索日?]);

投稿日時 - 2008-03-28 00:40:00

お礼

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

投稿日時 - 2008-03-29 16:47:00

あなたにオススメの質問