Access VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
1033 / 2272 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【9078】集計方法について あおぎん 07/2/1(木) 15:42 質問[未読]
【9080】Re:集計方法について 小僧 07/2/2(金) 10:40 回答[未読]
【9082】Re:集計方法について あおぎん 07/2/2(金) 16:12 お礼[未読]
【9078】集計方法について
質問  あおぎん  - 07/2/1(木) 15:42 -
引用なし
パスワード
   いつもお世話になっています。

毎月の給食費の支出を管理するデータベースを作っています。
毎月のデータを下のような構成のテーブル【T_支給明細_年間給食費18】に積み上げていって
名前　学校コード　月分　給食費　請求日
Ａ　　10　　　　　1　　5000 　　2007/01/31
Ａ　　10　　　　　12　 4800　　 2006/12/31
・
・
集計用にクロス集計クエリを作って次のような集計データを得ています。
学校コード　生徒名　・・　12_月分　12_月分請求日　1_月分　　1_月分請求日　・・
10　　　　　Ａ　　　・・　　4800　 　2006/12/31　　5000　　2007/01/31

・集計クエリのSQL
TRANSFORM Max(P.給食費計) AS 金額の最大
SELECT P.学校コード, P.生徒名
FROM [
SELECT T_支給明細_年間給食費18.生徒名, T_支給明細_年間給食費18.学校コード,T_支給明細_年間給食費18.月分 & "_月分" AS 集計,
T_支給明細_年間給食費18.給食費計, T_支給明細_年間給食費18.給食費請求日 AS 抽出
FROM T_支給明細_年間給食費18

UNION ALL SELECT T_支給明細_年間給食費18.生徒名, T_支給明細_年間給食費18.学校コード,T_支給明細_年間給食費18.月分 & "_月分請求日" AS 集計,
T_支給明細_年間給食費18.給食費請求日, T_支給明細_年間給食費18.給食費請求日 AS 抽出
FROM T_支給明細_年間給食費18

WHERE T_支給明細_年間給食費18.給食費請求日
]. AS P
WHERE (((P.抽出)>=[Forms]![F_給食費事務メニュー]![抽出給食費請求日])) OR ((([Forms]![F_給食費事務メニュー]![抽出給食費請求日]) Is Null))
GROUP BY P.学校コード, P.生徒名
ORDER BY P.学校コード
PIVOT P.集計 In ("4_月分","4_月分請求日","5_月分","5_月分請求日","6_月分","6_月分請求日","7_月分","7_月分請求日","9_月分","9_月分請求日","10_月分","10_月分請求日","11_月分","11_月分請求日","12_月分","12_月分請求日","1_月分","1_月分請求日","2_月分","2_月分請求日","3_月分","3_月分請求日");

今までとくに支障がなかったのですが、ときどき誤請求があって、訂正分を【T_支給明細_年間給食費18】に追加した場合
名前　学校コード　月分　給食費　請求日
Ａ　　10　　　　　1　　5000　　2007/01/31
Ａ　　10　　　　　12　 4800　　2006/12/31
Ａ　　10　　　　　1　　-500　　2007/02/16　　　←追加分

上記の集計方法だと
学校コード　生徒名　・・　12_月分　12_月分請求日　1_月分　　1_月分請求日　・・
10　　　　　Ａ　　　・・　4800　 　2006/12/31　　5000　 　　 2007/01/31
としか表示できず、-500円のデータが集計できません。

現在の自分の知識では、追加の月分を　1-1など枝番でテーブルに追加し、
学校コード　生徒名　・・　12_月分　12_月分請求日　1_月分　　1_月分請求日　1-1_月分　1-1月分請求日　として表示させるなどの方法しか思いつかないのですが、そうすると1月分を抽出したいときや、月ごとの並べ替えに都合が悪くなりそうで、
できれば、
学校コード　生徒名　・・　12_月分　12_月分請求日　1_月分　　1_月分請求日　・・
10　　　　　Ａ　　　・・　4800　 　2006/12/31　　　5000　 　 2007/01/31
10　　　　　Ａ　　　　　　　　　　　　　　　　　　　-500　　　2007/02/16

と表示するような方法があればと思うのですが・・。
もしご存知でしたらご教示をお願いします。

また、こういうケースを想定した場合のテーブルの構成方法や集計方法等でいい方法があればご教示いただけると大変助かります。

長々とすみません。
よろしくお願いいたします。
（Win 2000 Access2000）
【9080】Re:集計方法について
回答  小僧  - 07/2/2(金) 10:40 -
引用なし
パスワード
   ▼あおぎん さん：
おはようございます。

まず仕様の問題なのですが、

>名前　学校コード　月分　給食費　請求日
>Ａ　　10　　　　　1　　5000　　2007/01/31
>Ａ　　10　　　　　12　 4800　　2006/12/31
>Ａ　　10　　　　　1　　-500　　2007/02/16　　　←追加分

クロス集計をする際に1月分の給食費を4500円にされたいのは解るのですが、
請求日については 1/31、2/16 どちらを表示させたいのでしょうか。


考え方としては

>・集計クエリのSQL
>TRANSFORM Max(P.給食費計) AS 金額の最大
>SELECT P.学校コード, P.生徒名
>FROM [
>SELECT T_支給明細_年間給食費18.生徒名, T_支給明細_年間給食費18.学校コード,T_支給明細_年間給食費18.月分 & "_月分" AS 集計,
>T_支給明細_年間給食費18.給食費計, T_支給明細_年間給食費18.給食費請求日 AS 抽出
>FROM T_支給明細_年間給食費18
>
>UNION ALL SELECT T_支給明細_年間給食費18.生徒名, T_支給明細_年間給食費18.学校コード,T_支給明細_年間給食費18.月分 & "_月分請求日" AS 集計,
>T_支給明細_年間給食費18.給食費請求日, T_支給明細_年間給食費18.給食費請求日 AS 抽出
>FROM T_支給明細_年間給食費18
>
>WHERE T_支給明細_年間給食費18.給食費請求日
>]. AS P
>WHERE (((P.抽出)>=[Forms]![F_給食費事務メニュー]![抽出給食費請求日])) OR ((([Forms]![F_給食費事務メニュー]![抽出給食費請求日]) Is Null))
>GROUP BY P.学校コード, P.生徒名
>ORDER BY P.学校コード
>PIVOT P.集計 In ("4_月分","4_月分請求日","5_月分","5_月分請求日","6_月分","6_月分請求日","7_月分","7_月分請求日","9_月分","9_月分請求日","10_月分","10_月分請求日","11_月分","11_月分請求日","12_月分","12_月分請求日","1_月分","1_月分請求日","2_月分","2_月分請求日","3_月分","3_月分請求日");

UNION以前のSQLを抜き出して見ると

SELECT T_支給明細_年間給食費18.生徒名,
　　　 T_支給明細_年間給食費18.学校コード,
　　　 T_支給明細_年間給食費18.月分 & "_月分" AS 集計,
　　　 T_支給明細_年間給食費18.給食費計,
　　　 T_支給明細_年間給食費18.給食費請求日 AS 抽出
FROM T_支給明細_年間給食費18

↓

名前　学校コード　月分　給食費　請求日
Ａ　　10　　　　　1　　5000　　2007/01/31
Ａ　　10　　　　　12　 4800　　2006/12/31
Ａ　　10　　　　　1　　-500　　2007/02/16　　　←追加分


これを集計してあげれば良いのですから(2/16の日付を採るとすると)

SELECT T_支給明細_年間給食費18.生徒名,
　　　 T_支給明細_年間給食費18.学校コード,
　　　 T_支給明細_年間給食費18.月分 & "_月分" AS 集計,
　　　 Sum(T_支給明細_年間給食費18.給食費計) AS 給食費計,
　　　 Max(T_支給明細_年間給食費18.給食費請求日) AS 抽出
FROM T_支給明細_年間給食費18
GROUP BY T_支給明細_年間給食費18.生徒名,
　　　　 T_支給明細_年間給食費18.学校コード,
　　　　 T_支給明細_年間給食費18.月分 & "_月分";

↓

名前　学校コード　月分　給食費　請求日
Ａ　　10　　　　　1　　4500　　2007/02/16
Ａ　　10　　　　　12　 4800　　2006/12/31


こいつをクロス集計のクエリに使えばよいので

TRANSFORM Max(P.給食費計) AS 金額の最大
SELECT P.学校コード, P.生徒名
FROM [
SELECT T_支給明細_年間給食費18.生徒名,
　　　 T_支給明細_年間給食費18.学校コード,
　　　 T_支給明細_年間給食費18.月分 & "_月分" AS 集計,
　　　 Sum(T_支給明細_年間給食費18.給食費計) AS 給食費計,
　　　 Max(T_支給明細_年間給食費18.給食費請求日) AS 抽出
FROM T_支給明細_年間給食費18
GROUP BY T_支給明細_年間給食費18.生徒名,
　　　　 T_支給明細_年間給食費18.学校コード,
　　　　 T_支給明細_年間給食費18.月分 & "_月分"
UNION ALL
SELECT T_支給明細_年間給食費18.生徒名,
　　　 T_支給明細_年間給食費18.学校コード,
　　　 T_支給明細_年間給食費18.月分 & "_月分請求日" AS 集計,
　　　 T_支給明細_年間給食費18.給食費請求日,
　　　 T_支給明細_年間給食費18.給食費請求日 AS 抽出
FROM T_支給明細_年間給食費18
WHERE T_支給明細_年間給食費18.給食費請求日
]. AS P
WHERE (((P.抽出)>=[Forms]![F_給食費事務メニュー]![抽出給食費請求日])) OR
　　　((([Forms]![F_給食費事務メニュー]![抽出給食費請求日]) Is Null))
GROUP BY P.学校コード, P.生徒名
ORDER BY P.学校コード
PIVOT P.集計
In ("4_月分","4_月分請求日","5_月分","5_月分請求日",
　　"6_月分","6_月分請求日","7_月分","7_月分請求日",
　　"9_月分","9_月分請求日","10_月分","10_月分請求日",
　　"11_月分","11_月分請求日","12_月分","12_月分請求日",
　　"1_月分","1_月分請求日","2_月分","2_月分請求日",
　　"3_月分","3_月分請求日");


こんな感じになるのでしょうかね。
【9082】Re:集計方法について
お礼  あおぎん  - 07/2/2(金) 16:12 -
引用なし
パスワード
   ▼小僧 さん：
回答ありがとうございます。

>クロス集計をする際に1月分の給食費を4500円にされたいのは解るのですが、
>請求日については 1/31、2/16 どちらを表示させたいのでしょうか。

集計したときに、誤請求等で追加した分を別に表示できれば、見やすいなあと考えていたので、

学校コード　生徒名　・・　12_月分　12_月分請求日　1_月分　　1_月分請求日　・・
10　　　　　Ａ　　　・・　4800　 　2006/12/31　　　5000　 　 2007/01/31
10　　　　　Ａ　　　　　　　　　　　　　　　　　　　　-500　　　2007/02/16

こんな風にどちらも表示できたらなと思ったのです。
1〜12月で基本は１人ひと月につき１データなので、素人考えで、２つ以上ある場合、抽出して２行め（？）に集計する、といったことができるのかなと思いまして。

トータルの金額が表示できれば大満足なので、ご提示の方法でやってみます。
sumを使えばいいのですね。
説明がわかりやすく、よく理解できました。

ありがとうございました！
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
1033 / 2272 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
1078201
(SS)C-BOARD v3.8 is Free