Access：レポートでの集計で、DLookupをうまく合計するには

Question

実現したいこと

Access(2007-2016)で損益管理DBを作成しています。

レポートで数式を用いてレコードの集計を行っているのですが、なかなか思うように計算できなかったため、こちらで質問させていただきます

以下、詳細になります。

テーブルとリレーション

〇使用テーブルは6つで、以下のようにリレーションを組んでいます。

1.T_クライアント---------------
顧客ID:オートナンバー
顧客名:短いテキスト

顧客ID 顧客名
1 ○○商事
2 ○○広告

2.T_受注---------------
受注No:オートナンバー
受注日:日付
顧客ID:数値

受注No 受注日 顧客ID
1 2020/07/01 1
2 2020/08/01 1
3 2020/09/01 1

3.T_受注明細-------------
受注No:数値
業務内容:短いテキスト
予算額:数値

受注No 業務内容 予算額
1 クレーム処理 2000
1 営業代行 1000
2 クレーム処理 500
2 定期清掃 1500
3 クレーム処理 2000
3 定期清掃 1500

3.T_勤怠----------------
勤怠No:オートナンバー
作業日:日付
従業員ID:数値

勤怠No 作業日 従業員ID
1 2020/07/02 1
2 2020/07/03 2
3 2020/08/12 1
4 2020/08/13 1
5 2020/09/28 2
6 2020/09/28 1

5.T_稼働-----------------
稼働No:オートナンバー
勤怠No:数値
クライアントID:数値
業務内容:短いテキスト
稼働時間:数値

稼働No 勤怠No クライアントID 業務内容 稼働時間
1 1 1 クレーム処理 2
2 1 1 営業代行 2
3 2 1 クレーム処理 4
4 3 1 クレーム処理 1
5 4 1 定期清掃 3
6 5 1 クレーム処理 3
7 6 1 定期清掃 1
8 6 1 クレーム処理 3

6.T_従業員------------------
従業員ID:オートナンバー
従業員名:短いテキスト
時間単価:数値

従業員ID 従業員名 時間単価
1 たろう 10
2 はなこ 20

###クエリ
1.クエリ「Q_クライアント別_損益管理」:各月、誰がどのクライアントのどの業務に、どれだけ時間をかけたかのクロス集計クエリ

2.クエリ「Q_予算額」:各月、各クライアントの各業務の予算額はどれくらいかを見るクロス集計クエリ

###レポート


ここで冒頭の画像に戻ります。

内容としては、各月、各従業員がある業務にかけた時間を「稼働時間」とし、それに各従業員の時給をかけて「実績額」を出しています。また各月各業務の予算額を「Q_予算額」からDLookUpで持ってきて、「実績額」の合計との差額を「損益」としています。

**今回伺いたいのは黄色で塗りつぶした部分➀➁です。**➃は➀➁が理解できれば解決すると思われるので、黄色➀➁の算出方法を伺いたいです。以下、現状のコードです。

➂のコード
=DLookUp("予算額","Q_予算額","クライアントID=" & [クライアントID] & " AND 業務内容='クレーム処理' AND 契約月=[作業月]")

➀のコード
=Sum(DLookUp("予算額","Q_予算額","クライアントID=" & [クライアントID] & " AND 業務内容='クレーム処理' AND 契約月=[作業月]"))

➁のコード
(➀のコード)-Sum([クレーム処理]*[時間単価])

➂はDlookUpで引っ張ってきたコードなので、「稼働時間」と「実績額」のように単純に上位のグループヘッダー(フッター)でSumすればOKとはならないのだろうというのは分かって気でいるのですが、ではどうすればいいのか糸口が見えず、質問させていただきました。お力添えよろしくお願いいたします。SQLやVBAについては多少の理解があるので、それらを使って解決できるようならそれでも大丈夫です。

Answer 1

テキストボックスのコントロールソースに記述できるSum関数は
「フィールド名に限定されている」はずですので計算式は確か書けません。
テキストボックスのコントロールソースで式を書くのではなく
クエリ内で↓これを書いてフィールドとし

 クレーム予算額:DLookUp("予算額","Q_予算額","クライアントID=" & [クライアントID] & " AND 業務内容='クレーム処理' AND 契約月=[作業月]")

テキストボックス内は

 ➀のコード
=Sum([クレーム予算額])

にしないとダメかと。

ぱっと見でそもそも論になって申し訳ないですが
「T_稼働」テーブルの[業務内容]か「T_受注明細」の[業務内容]は死んでいる気がするのですが…
「T_稼働」テーブルに必要なのは[クライアントID]ではなく[受注No]では？
「T_稼働」の[受注No]と[業務内容]で「T_受注明細」の主キーに連結すべきかなと思います。

「T_受注明細」から[予算額]を持ってきているものの「T_稼働」には
[クライアントID]と[業務内容]しかなく、「同クライアントIDと同業務内容の別受注No」が
きたら対応できないような気がします。

Comments

[ryoya_access]

ご回答いただきありがとうございます。
当初の質問に加えて、「ぱっと見でそもそも論になって申し訳ないですが」以降のご指摘も大変ありがたかったです。一度テーブル、リレーションから見直したいと思います。ありがとうございました。

Answer 2

DLookUp()で返却されるのは1行のみです。
レポートでは、各々のヘッダーで配下に表示されているもの全ての集計については、コントロールソースに

=Sum([レコードソースの項目名])

のような記述が出来ます。※但し集計項目については上限があったはずですので、多用には注意が必要。
条件が必要なら、iif()で条件分岐させるか、

=Sum(iif(条件, [レコードソースの項目名], 0))

Dsum()などを用いれば良いかと。

Comments

[ryoya_access]

●DLookUp()で返却されるのは1行のみです>>>「=Sum(DLookup~」という書き方自体がおかしいということですね。これについては理解できました。

●レポートでは、各々のヘッダーで配下に表示されているもの全ての集計については、コントロールソースに=Sum([レコードソースの項目名])のような記述が出来ます。>>>

今回であれば、このレポートは「Q_クライアント別_損益管理」をレコードソースとしているため、クエリ「Q_クライアント別_損益管理」に含まれるレコードの集計は「=Sum([レコードソースの項目名])」というようにして簡単にできる。

しかし、別のクエリ「Q_予算額」からDLookupで持ってきたレコードを、「Q_クライアント別_損益管理」のレコードと同じように集計することはできない、という理解であっているでしょうか。

[sazi]

=DlookUP()のようにコントロールソースに指定して、その項目をSum()するようにすれば可能です。
ですけど、非常に重くなると思われますので、レコードソースを元に集計するレポートにした方が、後々の為には良いと思います。
集計の単位があっているなら、レコードソースに「Q_予算額」を追加すれば良いですし、
合ってないなら、「Q_予算額」を集計単位に合わせた新たなクエリーを作るなりして、レコードソースに追加すればいいでしょう。