愚者の経験

「また今度」はほとんどこない

月別アーカイブ: 12月 2014

[Access][ODBC][VBA]RefreshLinkとリンクテーブルマネージャでの再リンク

同じことやっているんだろう…そう思っていた時期が私にもありました。
Viewに適用するとRefreshLinkは主キーの擬似インデックスが外れますが、リンクテーブルマネージャは
外れません。なぜでしょう…これのせいでRefreshLinkがかなり使いづらいです。

広告

[Access][VBA]Accessの開発方法考察(模索中)

・単純かつ頻繁に出るコードは標準モジュールのPublic Functionに集める
フォームのオープン、クローズ
コンボボックスのカラム値を代入
リストボックスを複数選択した場合の値を連結
Tempvarsに値を代入
リボンの表示非表示切り替え
RGBを6桁で入力する
・ODBCでパススルークエリを使う場合はなるべくパススルークエリを直接
 フォームのレコードソースにせずにワークテーブルを利用する
(私が知らないだけかもしれないが)パススルークエリで行を最新に更新する場合
Requeryでパススルークエリの対象行全てを問い合わせるしかないため。
面倒だがトラフィックを考慮し、ワークテーブルに対して別のパススルークエリで
更新対象行のUpdate(またはDeleteとInsert)を行ってフォームのレコードを更新する。
パススルークエリが1行しか表示しない場合などはそのままでいいと思います。

[SQL Server][SQL][Tips]pivotを使ってAccessのクロス集計クエリっぽいことをする2

前回の続きというか応用

テーブル用意(前回と同じです)
pivott

Q.「いやね、金額と個数の両方合計してだしてくれない?」とか言われたらどうしますか?
A.イラッとする
見てのとおりpivotでは集計項目は1つしか選べません。なので同じデータを2回selectして
項目を変えればもちろん取得出来ます。

	select Y.*
		,(select top 1 A.売上先名称 from dbo.mtbl_売上先 as A where A.売上先ID=Y.売上先ID) as 売上先名称
	from (
		select A.売上先ID,B.商品名称,sum(A.金額) as 金額計
		from dbo.tbl_売上明細 as A
			inner join dbo.mtbl_商品 as B on B.商品ID=A.商品ID
			left join dbo.mtbl_商品分類 as C on C.分類ID=B.分類ID
		group by A.売上先ID,C.分類名称,B.商品名称
	) as X
	pivot (
		max(X.金額計) for X.商品名称 in 
			(みかん,りんご,トマト,きゅうり)
	) as Y

	select Y.*
		,(select top 1 A.売上先名称 from dbo.mtbl_売上先 as A where A.売上先ID=Y.売上先ID) as 売上先名称
	from (
		select A.売上先ID,B.商品名称,sum(A.個数) as 個数計--変更部分
		from dbo.tbl_売上明細 as A
			inner join dbo.mtbl_商品 as B on B.商品ID=A.商品ID
			left join dbo.mtbl_商品分類 as C on C.分類ID=B.分類ID
		group by A.売上先ID,C.分類名称,B.商品名称
	) as X
	pivot (
		max(X.個数計) for X.商品名称 in 
			(みかん,りんご,トマト,きゅうり)
	) as Y

あとは2つのビューを「inner join」なり「union all」なりすればいいのですが
「selectのコスト重すぎ…2回もできない…」という方、「inner join」を使う代わりにこんな方法もあります。

	select Y.*
	from (
                -- ※※※※※※※※※※※※
		select D.売上先ID
			,D.商品名称+E.Col as Col,E.Val
		from (
			select A.売上先ID,B.商品名称,sum(A.個数) as 個数計,sum(A.金額) as 金額計
			from dbo.tbl_売上明細 as A
				inner join dbo.mtbl_商品 as B on B.商品ID=A.商品ID
				left join dbo.mtbl_商品分類 as C on C.分類ID=B.分類ID
			group by A.売上先ID,C.分類名称,B.商品名称
		) as D
			cross apply (
				select '個数',D.個数計 union all
				select '金額',D.金額計
			) as E (Col,Val)
                -- ※※※※※※※※※※※※
	) as X
	pivot (
		max(X.Val) for X.Col in
			(みかん個数,りんご個数,トマト個数,きゅうり個数
			,みかん金額,りんご金額,トマト金額,きゅうり金額)
	) as Y

なんじゃこりゃぁ…とか思いますが私も最初はそうでした(笑)当然(?)ミソはcross applyの部分です。
この部分で「項目名」と「値」のセットをそれぞれunionします。
今回の場合、集計項目が「個数」と「金額」ですので、
項目名に’個数’というリテラル文字、値に「個数計=sum(個数)」
項目名に’金額’というリテラル文字、値に「金額計=sum(金額)」
となりこれらをunionし、cross applyします。

この時点でどうなっているかというと※の間のselectを実行すればわかりますが
売上先ID、Col、Val
10、りんご個数、sum(個数)
10、りんご金額、sum(金額)

というような形になって一行のデータを個数と金額の2行に分けています。
あとはpivotで(みかん,りんご,トマト,きゅうり)だったのを
(みかん個数,りんご個数,トマト個数,きゅうり個数,みかん金額,りんご金額,トマト金額,きゅうり金額)に
変更して値を集めているというわけです。機会があったらぜひ利用してみてください。

参考URL:http://stackoverflow.com/questions/18662012/sql-server-pivot-on-multiple-columns