FC2ブログ

クロス集計でどう? 


めっきり寒くなってきましたね。
今日は、雪もチラつきました・・・

そんな事はどうでも良いんで・・・・

以下の様な質問がありました

an商品金額担当A担当B
1 リンゴ300 田中小林
2 メロン1000 田中 
3 バナナ500 山本田中
4 マンゴー800 小林 
5 キウイ200 山本小林
というデータがあった時

田中_商品田中_金額小林_商品小林_金額山本_商品山本_金額
 バナナ250  キウイ100  キウイ100 
 メロン1000  マンゴー800  バナナ250 
 リンゴ150  リンゴ150  計350 
 計1400  計1050   
としたい・・・・

また、
担当金額
田中1400 
小林1050 
山本350 
2800 
っていうものも作りたい・・・・

つまり、金額は、
・担当A のみの時には丸々
・担当B も埋まっていたら、折半
・・・・

で、私が回答した内容は以下になるのですが・・・

テーブル名を ★★ と仮定します。
SQL ビューで記述します。

1つ目(やりたい事と違うかもしれませんが、添付図上側)
クロス集計を使います。

TRANSFORM Sum(金額) AS 値
SELECT 商品 FROM (
SELECT 0 AS S, 商品, IIF(担当B Is Null,★★.金額,★★.金額\2) AS 金額, 担当A AS 担当 FROM ★★
UNION ALL
SELECT 0, 商品, 金額\2, 担当B FROM ★★ WHERE 担当B Is Not Null
UNION ALL
SELECT 1, '計', IIF(担当B Is Null,金額,金額\2), 担当A FROM ★★
UNION ALL
SELECT 1, '計', 金額\2, 担当B FROM ★★ WHERE 担当B Is Not Null
) AS Q1
GROUP BY S, 商品
PIVOT 担当;

2つ目

SELECT 担当, Sum(Q1.金額) AS 金額 FROM (
SELECT 0 AS S, IIF(担当B Is Null,★★.金額,★★.金額\2) AS 金額, 担当A AS 担当 FROM ★★
UNION ALL
SELECT 0, 金額\2, 担当B FROM ★★ WHERE 担当B Is Not Null
UNION ALL
SELECT 1, IIF(担当B Is Null,金額,金額\2), '計' FROM ★★
UNION ALL
SELECT 1, 金額\2, '計' FROM ★★ WHERE 担当B Is Not Null
) AS Q1
GROUP BY S, 担当;

※ 1つ目、2つ目でのデータの並び順(計金額の多い人順)を考えているのなら
 1つ目については VBA を使う事になると思います。
 2つ目については、最後の
GROUP BY S, 担当
の後に
ORDER BY S, Sum(Q1.金額) DESC
を追加すれば良いと思います。

※ 1つ目はやりたい事と違うと思いますので、他の方の回答を待ってください。
 また、レポート上の事であれば他の方の回答を待ってください。

で、添付していた画像は

y_q14116172494.jpg

レポートの事はてんでわかりません。
データの再利用を主に、Excel へ展開するのが多いので・・・ チンプンカンプン

回答において、

※ 1つ目、2つ目でのデータの並び順(計金額の多い人順)を考えているのなら
 1つ目については VBA を使う事になると思います。

と記述していた部分を、詳しく?書いてみたいと思います。
続きを読んでみようかな ---≫
スポンサーサイト

2013/11/11

Category: クエリ

TB: --  /  CM: 0

top △

クエリで再帰処理?その2 


クエリの再帰処理については クエリで再帰処理? でも取り上げていました。

やはり、ユーザ定義関数を使っちゃいますね。

11112222
22223333
22224444
33335555
55556666
55557777
66662222
というテーブル「TA」があり (「親」「子」とも長整数)

結果
 1111  2222
         2222  3333
                 3333  5555
                         5555  6666
                                 6666  2222*
                         5555  7777
         2222  4444

のように表示したい。

オートナンバを持つワーク用のテーブル「TB」を使えば楽そうです。
(以下はオートナンバ部分を削除した感じ)
iNstSrc
01111  2222
12222  3333
23333  5555
35555  6666
46666  2222*
35555  7777
12222  4444
iNst :長整数
Src :テキスト型

今回、Dictionary を使って循環チェックしてみましょう・・・ ということで
続きを読んでみようかな ---≫

2012/01/26

Category: クエリ

TB: 0  /  CM: 0

top △

重複時間帯を求めてみる 


以下のようなデータがあった場合、
氏名入室時刻退室時刻
aaa2011/10/01 11:002011/10/01 17:00
bbb2011/10/01 13:002011/10/01 15:00
ccc2011/10/01 14:002011/10/01 15:30
ddd2011/10/01 17:002011/10/01 19:00
eee2011/10/01 9:002011/10/01 11:00
fff2011/10/01 19:002011/10/01 20:30

入室時刻、退室時刻内で他の人と重複していた時間帯を以下のように求めたい。
氏名重複入室重複退室
aaa2011/10/01 13:002011/10/01 15:30
bbb2011/10/01 13:002011/10/01 15:00
ccc2011/10/01 14:002011/10/01 15:30

この元のデータをテーブル「T1A」に作ったとして
SELECT Q1.*, Q2.*
FROM T1A AS Q1, T1A AS Q2
WHERE Q1.氏名<>Q2.氏名 AND Q1.入室時刻<Q2.退室時刻 AND Q1.退室時刻>Q2.入室時刻;
とすれば、誰に対して誰が重複しているか判別できますね。
この表示結果は以下になります。
Q1.氏名Q1.入室時刻Q1.退室時刻Q2.氏名Q2.入室時刻Q2.退室時刻
aaa2011/10/01 11:002011/10/01 17:00bbb2011/10/01 13:002011/10/01 15:00
aaa2011/10/01 11:002011/10/01 17:00ccc2011/10/01 14:002011/10/01 15:30
bbb2011/10/01 13:002011/10/01 15:00aaa2011/10/01 11:002011/10/01 17:00
bbb2011/10/01 13:002011/10/01 15:00ccc2011/10/01 14:002011/10/01 15:30
ccc2011/10/01 14:002011/10/01 15:30aaa2011/10/01 11:002011/10/01 17:00
ccc2011/10/01 14:002011/10/01 15:30bbb2011/10/01 13:002011/10/01 15:00

この結果を元に、Q1、Q2 の入室時刻、Q1、Q2 の退室時刻をいじれば求まりますね。
続きを読んでみようかな ---≫

2011/10/09

Category: クエリ

TB: 0  /  CM: 0

top △

クエリ 過去平均フィールド追加の模索 


テーブル「TBL_A」が、日付、売上 で構成されているとした時、
その日以前の4日分の平均をフィールドとして追加した表示をしたい・・・・ってなった時、どうすればよい?
4日を限定しないで、4件分を・・・
また、4件なかったらある分だけで・・・

日付売上平均
2011/07/01100 
2011/07/02200 7/1 の値
2011/07/03300 7/1 ~ 7/2 の平均
2011/07/04400 7/1 ~ 7/3 の平均
2011/07/05500 7/1 ~ 7/4 の平均
2011/07/06600 7/2 ~ 7/5 の平均
2011/07/07700 7/3 ~ 7/6 の平均
2011/07/08800 7/4 ~ 7/7 の平均
2011/07/09900 7/5 ~ 7/8 の平均
2011/07/101000 7/6 ~ 7/9 の平均
続きを読んでみようかな ---≫

2011/07/31

Category: クエリ

TB: 0  /  CM: 0

top △

クエリで再帰処理? 


ある質問で、以下のようなテーブル「TA」があり、
F1F2
AAABBB
AAACCC
AAAXXX
BBBXXX
BBBYYY
CCCDDD
DDDYYY
XXXZZZ

F1="AAA" の条件時には、F1="AAA" の F2 に対して、その F2 を F1 とみなして、
さらに、その F1 の F2 に対しても同様に F1 とみなして・・・・
F1 にあるものを表示しようとするもの。

F1="AAA" 条件時には、
F1F2
BBBXXX
BBBYYY
CCCDDD
DDDYYY
XXXZZZ

F1="BBB" 条件時には、
F1F2
XXXZZZ

同様に F1="CCC" 条件時には、
F1F2
DDDYYY


これを SQL だけで・・・・って、わかりません。
残念ながら、回答できるレベルにありません。

再帰処理すればいいんだろうなぁ・・・くらいですね、思いつくのは・・・

Access の SQL って言っているので、ユーザ定義関数を使っても・・・・
それなら再帰を使った例を2つ位提示できるんですけど・・・  (うっ、例にならないかも)

動くというだけで、処理性能は未知です。
続きを読んでみようかな ---≫

2011/06/23

Category: クエリ

TB: 0  /  CM: 0

top △

テーブル間の部分一致結合 


旧記事掲載:2010/08/05

出来上がるクエリは更新できないものになりますが、以下の様な事を考えてみました。

テーブル「TA1」が以下だとします。
anF1
1神奈川県横浜市中区YYY-ZZZ
2東京都港区赤坂YYY
3神奈川県横浜市YYY-ZZZ

テーブル「TB1」が以下だとします。
anF1F2
1中央区0011
2港区0012
3横浜市中区0033
4横浜市南区0034

この時、

F1F2
神奈川県横浜市中区YYY-ZZZ0033
東京都港区赤坂YYY0012

とするクエリの内容は、
SELECT TA1.F1, TB1.F2
FROM TA1 INNER JOIN TB1 ON TA1.F1 Like "*" & TB1.F1 & "*";
になります。

また、一致しなくても「TA1」のものは表示して欲しい場合には、
SELECT TA1.F1, TB1.F2
FROM TA1 LEFT JOIN TB1 ON TA1.F1 Like "*" & TB1.F1 & "*";
となり、結果は以下の様になります。

F1F2
神奈川県横浜市中区YYY-ZZZ0033
東京都港区赤坂YYY0012
神奈川県横浜市YYY-ZZZ 


INNER JOIN / LEFT JOIN を用途によって切り分け、ON で指定する部分を工夫すれば良さそうです。

やってみて動いたので、良しとしますか・・・
以前には直積のものを回答したことがあったかと・・・・
続きを読んでみようかな ---≫

2011/05/05

Category: クエリ

TB: 0  /  CM: 0

top △

クロス集計での(平均)行付加方法の模索 


旧記事掲載:2011/01/20

クロス集計で、横方向の「合計」や「平均」の列を追加することは簡単ですが、
縦方向の「合計」「平均」の行を追加するには?
「合計」の例は Web 上で検索できましたが、「平均」の例は探せませんでした。
(検索は下手です)

テーブルの内容は、以下のようになっているとします。
an:オートナンバ 
番号:数値型でもテキスト型でも 2007確認時は数値型
点数:数値型(長整数でも整数でも) 2007確認時は整数

テーブル「TBL_A」
an番号教科点数
1111英語10
2222国語30
3222数学50
4111国語70
5333数学90
6222英語80
7111数学60
8333英語40
9333国語20

このテーブルを元にクロス集計で

単なるクロス集計+平均行
番号合計英語国語数学
111140107060
222160803050
333150402090
番号合計英語国語数学
111140107060
222160803050
333150402090
平均150434066

としたいとします。
続きを読んでみようかな ---≫

2011/05/04

Category: クエリ

TB: 0  /  CM: 0

top △