Access もっと簡単な抽出方法はありますか？

質問

お世話になります。
Access2010

抽出方法についてご教示下さい。下記のテーブルがあります。
＜社員テーブル＞
社員番号　氏名　　　性別　　血液型
00100　　山田太郎　　男性　　A
00200　　鈴木一郎　　男性　　A
00300　　田中次郎　　男性　　B
00400　　鈴木花子　　女性　　O

＜人事テーブル＞
社員番号　人事C　　　部署名C　　開始日　　　　終了日
00100　　 1　　　　　　　　　　 2000/04/01　　2000/04/01
00100　　 2　　　　　2　　　　　2000/04/01　　2005/03/31
00100　　 3　　　　　1　　　　　2005/04/01　　2008/04/01
00100　　 3　　　　　3　　　　　2008/04/01
00200　　 1　　　　　 　　　　　2005/04/01　　2004/04/01
00200　　 2　　　　　1　　　　　2005/04/01　　2007/03/31
00200　　 3　　　　　3　　　　　2008/04/01
00300　　 1　　　　　 　　　　　2010/10/01　　2010/10/01
00300　　 2　　　　　2　　　　　2010/10/01
00400　　 1　　　　　　　　　　 2011/04/01　　2011/04/01
00400　　 2　　　　　1　　　　　2011/04/01　　2015/03/31
00400　　 4　　　　　　　　　　 2015/03/31　　2015/03/31

※人事Cの1(入社)の登録では部署名Cは登録されず、別レコードに
　登録されます。

＜人事マスタ＞
人事C　人事
　1　　入社
　2　　人事
　3　　部署移動
　4　　退職

＜部署マスタ＞
部署名C　　部署名
　1　　　　営業部
　2　　　　経理部
　3　　　　総務部

最終的にフォーム上のリストボックスに下記にように表示したいところです。
社員番号　部署名　氏名　　　性別　血液型
00100　　 総務部　山田太郎　男性　　A
00200　　 総務部　鈴木一郎　男性　　A
00300　　 経理部　田中次郎　男性　　B
00400　　 営業部　佐藤花子　女性　　O

下記のようにやって、とりあえず抽出はできたのですが。。
もっとよい方法があればお教え頂けたらと思います。

1.人事テーブルで人事Cが2 or 3で抽出し、グループ化、かつ開始日の最大
　でクエリを作成
　　00100　2008/04/01
　　00200　2008/04/01
　　00300　2010/10/01
　　00400　2011/04/01

2.1のクエリと人事テーブルを社員番号と開始日で紐づけてクエリ作成
　社員番号　　部署名C　　　開始日　　
　00100　　　　3　　　　　2008/04/01
　00200　　　　3　　　　　2008/04/01
　00300　　　　 　　　　　2010/10/01
　00300　　　　2　　　　　2010/10/01
　00400　　　　　　　　　 2011/04/01
　00400　　　　1　　　　　2011/04/01

　このように、00300と00400については入社のレコードも抽出されて
　しまうので、ここでさらに部署名CをIs Not Nullとする。
　00100　　　　3　　　　　2008/04/01
　00200　　　　3　　　　　2008/04/01
　00300　　　　2　　　　　2010/10/01
　00400　　　　1　　　　　2011/04/01

3.社員テーブルと2のクエリ、部署マスタを紐付けます。
　社員テーブル---------2のクエリ---------部署マスタ
　　　　　　（社員番号）　　　 （部署名C）
　このクエリをリストボックスのソースとしてます。

なお、実際には1と2のクエリはひとつにまとめてます。
SELECT Q.社員番号, 人事テーブル.部署名C
FROM (SELECT 人事テーブル.社員番号, Max(人事テーブル.開始日) AS 開始日の最大 FROM 人事テーブル WHERE (((人事テーブル.人事C)=2 Or (人事テーブル.人事C)=3)) GROUP BY 人事テーブル.社員番号) AS Q INNER JOIN 人事テーブル ON (Q.開始日の最大 = 人事テーブル.開始日) AND (Q.社員番号 = 人事テーブル.社員番号)
WHERE (((人事テーブル.部署名C) Is Not Null));

もっとシンプルに結果を出せるのでは？ということで質問させて
頂いた次第です。

宜しくお願い致します。

回答No.3

ushi2015 · ベストアンサー

こんにちは
結果が正しいのか良く分からないのですが、
SELECT A.社員番号, A.部署名, A.氏名, A.性別, A.血液型
FROM (SELECT 社員テーブル.社員番号, 部署マスタ.部署名, 社員テーブル.氏名, 社員テーブル.性別, 社員テーブル.血液型, 人事テーブル.開始日 FROM ((社員テーブル INNER JOIN 人事テーブル ON 社員テーブル.社員番号 = 人事テーブル.社員番号) INNER JOIN 人事マスタ ON 人事テーブル.人事C = 人事マスタ.人事C) INNER JOIN 部署マスタ ON 人事テーブル.部署名C = 部署マスタ.部署名C WHERE ((Not (人事テーブル.部署名C) Is Null) AND (Not (人事テーブル.開始日) Is Null))) AS A INNER JOIN (SELECT 人事テーブル.社員番号, Max(人事テーブル.開始日) AS 開始 FROM 人事テーブル WHERE ((Not (人事テーブル.部署名C) Is Null)) GROUP BY 人事テーブル.社員番号) AS B ON (A.開始日 = B.開始) AND (A.社員番号 = B.社員番号);
とか。

補足

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

クエリAで開始日と部署名Cが埋まっているレコードを抽出しておく。
※このクエリだけでは部署移動分のレコードまで抽出される

クエリBで社員番号毎に開始日の最大で抽出。

AとBを社員番号と開始日で紐付けて、現状の部署名を表示・・って
ことですね。

教えて頂いたSQLでちゃんと正しい結果がでました。

1点確認させて下さい。
クエリAで、人事マスタが紐づいてますが必要でしょうか。

回答No.4

ushi2015

こんにちは
人事マスタ紐付け不要です。
最初に全部関連付けて作ったクエリ参考にしたのに消し忘れました。

お礼

なるほどですね。了解です。

大変勉強になりました。
ありがとうございました。

回答No.2

panacon

終了日をKeyにできないのであれば、現在ステータスとして、Yes/NoのKeyをつけて、これが必ず一人にひとつとなるように組み立ててみてはと思います。

補足

別途フラグ付けすれば容易に抽出できることは
分かってます。

なお、実際にフラグを設けなくても抽出はできて
おります。
ただ、もっとシンプルにできないか・・という質問です。

回答No.1

panacon

人事テーブルの終了日を厳格に管理すると良いと思います。
退職者以外は、現在の位置が必ずあるので、現在の位置の終了日は、必ずブランクになると思います。個々の終了日のブランクが1つしかできないような工夫をすることを考えた方が良いです。これができれば、人事テーブルと社員テーブルを連結して、抽出キーは、終了日をis null にすれば良いと思います。

補足

panaconさま
ご回答ありがとうございます。
終了日Is Nullで抽出できるのであれば質問はしません。

見て頂くと分かると思いますが、退職者(00400)も
抽出しなければなりません。