accessのレポートについて教えてください。

Question

レポートのレコードソースには1人に対しいくつものレコードがあります。
そのレコード数はその人によって違います。

例えば
社員番号　　　発令日　　　　　　異動部署
123　　　　　2012年3月1日　　　営業3課
123　　　　　2018年3月1日　　　営業2課
123　　　　　2020年3月1日　　　営業1課

のようなデータがあります。

レポートで辞令を出したいとき
元の部署と新しい部署を記載するのですが、上の例でいうと元の部署を営業2課、新しい部署を営業1課として出力したいです。

文書例としては、
2020年3月1日をもって営業2課から営業1課の勤務を命じます。

このように辞令の中に表記させたいです。

どのようにしたら新しい部署と新しい部署が登録されている1つ前のレコードをレポートに呼びだせるでしょうか？

教えていただけると幸いです。

Comments

[kitasue]

この質問は解決したのですか。
https://teratail.com/questions/301337

[bee96]

まだ試行錯誤しておりますが・・・？
解決したらベストアンサー選びますよ・・・

Answer 1

自己結合する訳なので、それぞれの情報を繋ぐ情報を作り出す必要があります。
元にするテーブルを「異動」テーブルとすると、以下のようなSQLで取り出す事が出来ます。

元にするテーブルを「異動」テーブルとすると、
select 異動基準.*, 異動前.異動部署
from (
        select *
             ,(select max(発令日) 
               from 異動 as 異動前 
               where 異動前.社員番号=異動.社員番号 
                 and 異動前.発令日<異動.発令日
              ) as 異動前発令日
        from 異動
     ) as 異動基準
     left join 異動 as 異動前
     on  異動基準.社員番号=異動前.社員番号 and 異動基準.異動前発令日=異動前.発令日
     

時間が掛かるようなら、SQL中にある異動前発令日をテーブル作成クエリーで作成するとか、インデックスの見直しを行って下さい。

Comments

[bee96]

ありがとうございます。

異動前や移動基準といったフィールドはありませんが大丈夫でしょうか？

[sazi]

「異動前」や「異動基準」というのはSQL上のテーブルの別名ですので影響はしません。
影響するのはSELECTの項目名ですので、環境に合わせて適宜変更して下さい。

Answer 2

テーブルをレコードソースとするレポート上にテキストボックスを３つ配置して下記のように設定します。
テーブル名は「移動履歴」と仮定してます。

名前　前発令日
コントロールソース
=DMax("発令日","異動履歴","社員番号=" & [社員番号] & " AND 発令日<#" & [発令日] & "#")
可視　いいえ

---

名前　前部署
コントロールソース
=DLookUp("異動部署","異動履歴","社員番号=" & [社員番号] & " AND 発令日=#" & Nz([前発令日],"1000/1/1") & "#")
可視　いいえ

---

名前　辞令
コントロールソース
=Format([発令日],"yyyy年m月d日") & "をもって" & [前部署] & IIf([前部署] Is Null,"","から") & [異動部署] & "の勤務を命じます。"
可視　はい

---

以上です。

辞令文を自由に入力、編集したい場合

辞令文格納用のテーブルを作成します。テーブル名「辞令文マスター」
フィールドの型は「長いテキスト」にします。フィールド名「辞令文」

このテーブルをレコードソースとするフォームを作成して、テキストボックスを配置してコントロールソースは「辞令文」にします。
このフォームを保存します。名前は「F_辞令文」とします。

フォームを開いて辞令文を入力します。
入力例

{発令日}をもって{前部署}から{異動部署}の勤務を命じます。

改行も入力可です。{発令日} {前部署} {異動部署} というように参照する項目を{}で囲むのがポイントです。
{}でなくても 【】とか[]とかでもいいです。

このテーブルをレポートのレコードソースのクエリに追加します。連結はしません。

テキストボックス「前発令日」「前部署」は上記の説明のままで、テキストボックス「辞令」のコントロールソースを下記のように設定します。

= Replace(Replace(Replace([辞令文],"{発令日}",Format([発令日],"yyyy年m月d日")),"{前部署}",[前部署]),"{異動部署}",[異動部署])

---

ちなみに、上記では前部署を取得するのにDMax関数、DLOokup関数を使いましたが、サブクエリを使って同様に取得することもできます。SQLが理解できるならこれでもいいでしょう。

SELECT
    a.*,
    異動履歴.異動部署 AS 前部署
FROM
    (
        SELECT b.社員番号, b.異動部署, b.発令日,Max(c.発令日) AS 前発令日
        FROM 異動履歴 AS b INNER JOIN 異動履歴 AS c ON b.社員番号 = c.社員番号
        WHERE c.発令日 < b.発令日
        GROUP BY b.社員番号, b.異動部署, b.発令日
    ) AS a 
    INNER JOIN 異動履歴 As d
    ON a.前発令日 = d.発令日 AND a.社員番号 = d.社員番号;

　
あるいは、

SELECT
 b.*,
 ( SELECT TOP 1 異動部署 FROM 移動履歴 AS b
   WHERE a.社員番号 = b.社員番号 AND a.発令日 < b.発令日
   ORDER BY 発令日 DESC
 ) AS 前部署
FROM 異動履歴 AS a;

Comments

[bee96]

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

これだと自由な書式で文章が書けないということでしょうか？
私が出した文章はあくまで一例ですので、実際は文章を改行したりと色々行うつもりです。

[hatena19]

回答に追記しましたのでご参照ください。

[sousuke]

横からすみません。人1人に対して複数レコードある場合と１行しかない場合で
処理が違ってくるのでそこら辺をどうするつもりか明確にしたほうがいいのでは？

１行しかないと前部署が存在しないのでそれを加味して
「実際は文章を改行したりと色々行うつもり」ということでしょうか？

[bee96]

hatena様
丁寧な対応ありがとうございます。
こちらで一度作成してみます。

sousuke様
＞１行しかないと前部署が存在しない
前部署が存在しないのに異動の辞令は出ないのでそこは大丈夫です。
前部署がない＝入社直後となるのでその場合は配属の辞令が発行されます。

[hatena19]

異動の辞令 と 配属の辞令 の区別を考慮するなら、
辞令マスターに「種別」フィールドを追加してそれで区別するようにするといいですね。
レポートを開く時に抽出条件で、発令日 と 種別 でフィルターをかけるようにすればいいでしょう。

[bee96]

hatena様
= Replace(Replace(Replace([辞令文],"{発令日}",=Format([発令日],"yyyy年m月d日")),"{前部署}",[前部署]),"{異動部署}",[異動部署])

こちらの式ですが前部署というフィールドはなく、異動部署に過去の部署なども格納されているのですがこれでも大丈夫なのでしょうか？

[hatena19]

回答の前半部分のテキストボックスはそのまま配置しておいてください。名前か「前発令日」「前部署」のテキストボックスです。
テキストボックス「辞令」のコントロールソースをその式にします。
「前部署」はレポート上のコントロールを参照してますので大丈夫です。

[bee96]

hatenas様
実際に行いましたがうまく作動してくれません。
レポートビューを開こうとすると辞令文というパラメータが出てきます。
無視してOKと押すと何も入力されず、何か文字をいれると辞令のテキストボックスに入力した文字が反映されます。
しかしフォームで入力された文章は反映されませんでした・・・

[hatena19]

レポートのレコードソースに「辞令文マスター」を追加して「辞令文」フィールドを追加してますか。

[bee96]

ありがとうございます。
クエリを追加したのに辞令文自体を追加するのわすれていたみたいです・・・
ちゃんと反映できました！
フォーム自体で書体の変更ができず、レポートで確認しながら書体などをそろえる形になるので少し手間ですが慣れるまで頑張ってみようと思います・・・

また、氏名を追加しようとこの式に追加したのですが
=Replace(Replace(Replace([辞令文],"【発令日】",Format([発令日],"yyyy\年m\月d\日")),"【前部署】",[前部署]),"【部署】",[部署]),"【氏名】",[氏名])
テキストデータを引用符で囲む必要がありますと出ます。
訂正箇所を教えて頂けると幸いです。

[hatena19]

=Replace(Replace(Replace(Replace([辞令文],"【発令日】",Format([発令日],"yyyy\年m\月d\日")),"【前部署】",[前部署]),"【部署】",[部署]),"【氏名】",[氏名])
でどうかな。
項目数分Replaceが必要です。

[bee96]

できました！！
本当にありがとうございました。

最後に、辞令は異動だけではなく色々なものがあります。
例えば昇格の辞令を作ろうとしたとき、種別ごとにフォームに入力すると思うのですが、レポートを開く時の抽出条件の方法を教えて頂きたいです。
申し訳ありませんがあと少しお力を貸して頂きたいです。

Answer 3

レコードソースを基にして2個のクエリを作成。(ＡクエリとＢクエリとします。)
このクエリを社員番号で結合したクエリをさらに1個作ります。
この結合クエリをソースとしてレポートで読み込ませる事で可能と思います。

単純に結合した場合には、このケースでは9レコードが表示される筈です。
後は、結合クエリに対して発令日で条件を絞って1レコードが出るようにします。
例えば：
Ａ.発令日<>Ｂ.発令日
Ａ.発令日>=2018年3月1日
Ｂ.発令日>=2020年3月1日
といった条件を付けてレコードを絞り込みます。
条件は、データの種類や運用方法で色々と出来ると思いますので、試してみて下さい。

Answer 4

こんにちは。
ベタなやり方かもしれませんが、、、
社員番号・発令日・異動部署のテーブルをAとして
社員番号・発令日・異動前部署・異動後部署というテーブルBを作成し、テーブルBをレポートに関連付けします。

1. テーブルBをdeleteします。
2. テーブルAを社員番号・発令日順にSelectします。
3. SelectしたテーブルAのレコードを順に取得します。（ループ処理）
4. テーブルBのレコードを追加します。(AddNew)
5. 社員番号・発令日・異動部署をそれぞれテーブルBの社員番号・発令日・異動後部署に設定します。
6. 移動後部署を保持します。
7. ループの初回または前回と社員番号が異なる場合、移動前部署には何も設定せず、そうでない場合保持している異動後部署を異動前部署に設定します。
8. テーブルBのレコードを確定(UPDATE)します。

これでレポートを出力すれば良いと思います。