VBAからGDI+を使う資料集
inet上の達人の方々から御教示いただいたコードを少しアレンジさせてもらっています（切り貼りとも言います）。
  1. ホーム
  2. Other
  3. learnquery


自テーブルを参照するクエリのお勉強

近頃Excel VBAからADOで自ブックに接続するのに凝っています。
xl2007以降は、自ブックに接続するとメモリリークというトラブルは解消されたそうですので。
という事で、もう少し複雑なクエリを使ってみたいと、お勉強のために D関数の機能をクエリでやってみました
最初はいきなりSQLビューに打ち込んでいたのですが、デザインビューの機能を生かすと便利かつ理解しやすい事が分かりました。

こんなテーブルTable1を用意します。

Code OrderDate Price
A 2014/01/01 10
A 2014/01/02 20
A 2014/01/03 30
A 2014/01/04 40
B 2014/01/05 11
B 2014/01/06 21
B 2014/01/07 31
B 2014/01/08 41
C 2014/01/09 12
C 2014/01/10 22
C 2014/01/11 32
C 2014/01/12 42

クエリービューで配置して、全フィールドを表示する設定をします。

参照用にTable1をもう一個置きます。名前がTable1_1とつけられます。

SELECT Table1.Code, Table1.OrderDate, Table1.Price
FROM Table1, Table1 AS Table1_1;

Code OrderDate Price
A 2014/01/01 10
A 2014/01/02 20
A 2014/01/03 30
A 2014/01/04 40
B 2014/01/05 11
B 2014/01/06 21
B 2014/01/07 31
B 2014/01/08 41
C 2014/01/09 12
C 2014/01/10 22
C 2014/01/11 32
C 2014/01/12 42
A 2014/01/01 10
・・・・

データは3*4*3*4=122個の組み合わせとなります

簡便のためにテーブルに別名をつけます
DはデータのD、RはリファレンスのRのつもりで、Rの条件を基準にDから抽出すると考えます

SELECT D.Code, D.OrderDate, D.Price
FROM Table1 AS D, Table1 AS R;

DとRでCodeが同じ物を対象とするので、
D.Codeの抽出条件にR.Codeを入れると
SELECT D.Code, D.OrderDate, D.Price
FROM Table1 AS D, Table1 AS R
WHERE (((D.Code)=[R].[Code]));

となり、

Code OrderDate Price
A 2014/01/04 40
A 2014/01/03 30
A 2014/01/02 20
A 2014/01/01 10
A 2014/01/04 40
A 2014/01/03 30
A 2014/01/02 20
A 2014/01/01 10
A 2014/01/04 40
A 2014/01/03 30
A 2014/01/02 20
A 2014/01/01 10
A 2014/01/04 40
A 2014/01/03 30
A 2014/01/02 20
A 2014/01/01 10
B 2014/01/08 41
・・・・

データは4*3*4=48個の組み合わせとなります

抽出条件を指定する代わりに両テーブルのCode同士を結合線で結ぶと
SELECT D.Code, D.OrderDate, D.Price
FROM Table1 AS D INNER JOIN Table1 AS R ON D.Code = R.Code;
となり、得られる結果は同じとなります。
さて、元に戻って、抽出されるデータを確認するために、D,Rの全フィールドを表示させます。

SELECT D.Code, D.OrderDate, D.Price,R.Code,R.OrderDate,R.Price
FROM Table1 AS D, Table1 AS R
WHERE (((D.Code)=[R].[Code]));

D.Code D.OrderDate D.Price R.Code R.OrderDate R.Price
A 2014/01/01 10 A 2014/01/03 30
A 2014/01/01 10 A 2014/01/04 40
A 2014/01/01 10 A 2014/01/01 10
A 2014/01/01 10 A 2014/01/02 20
A 2014/01/02 20 A 2014/01/04 40
A 2014/01/02 20 A 2014/01/03 30
A 2014/01/02 20 A 2014/01/01 10
A 2014/01/02 20 A 2014/01/02 20
A 2014/01/03 30 A 2014/01/04 40
A 2014/01/03 30 A 2014/01/03 30
A 2014/01/03 30 A 2014/01/02 20
A 2014/01/03 30 A 2014/01/01 10
A 2014/01/04 40 A 2014/01/02 20
A 2014/01/04 40 A 2014/01/03 30
A 2014/01/04 40 A 2014/01/04 40
A 2014/01/04 40 A 2014/01/01 10
B 2014/01/05 11 B 2014/01/06 21
・・・・

基準R.OrderDateを一個選んで、それ以下のD.OrderDateの個数を数えます。
D.OrderDateの抽出条件に<=R.OrderDateを追加します。

D.Code D.OrderDate D.Price R.Code R.OrderDate R.Price
A 2014/01/01 10 A 2014/01/01 10
A 2014/01/02 20 A 2014/01/02 20
A 2014/01/01 10 A 2014/01/02 20
A 2014/01/03 30 A 2014/01/03 30
A 2014/01/02 20 A 2014/01/03 30
A 2014/01/01 10 A 2014/01/03 30
A 2014/01/04 40 A 2014/01/04 40
A 2014/01/03 30 A 2014/01/04 40
A 2014/01/02 20 A 2014/01/04 40
A 2014/01/01 10 A 2014/01/04 40
B 2014/01/05 11 B 2014/01/05 11
・・・・

30個が抽出されました。
ここでDから抽出するならばD.OrderDateを用いれば良いのですが、条件を満足する個数をカウントするには、上記の通り、R.OrderDateの方をカウントする必要がある事が分かります。
集計クエリーに変更してみます R.OrderDateをグループ→カウントに変更して結果を見ようとしますが、下記エラーになります。

集計関数の一部として指定された式 'D.Code=R.Code And D.OrderDate<=[R].[OrderDate]'
を含んでいないクエリを実行しようとしました。

D.Codeと、D.OrderDateには抽出条件を設定してありますので、集計をグループ化→Where条件に変更する必要があります。この操作を行うと表示のチェックが外れてしまいます。このチェックを戻してクエリを実行しようとすると、下記のエラーになります。

[集計] 行の [Where 条件] に指定されているフィールドを表示できません。このフィールドの [表示] チェック ボックスをオフにしてください。クエリの結果にこのフィールドを表示するには、デザイン グリッドに 2 回追加します。クエリの結果に表示されるフィールドでは、[集計] 行 [Where 条件] を指定しないようにし、[表示] チェック ボックスをオンにします。

指示の通りに、D.CodeおよびD.OrderDateのフィールドを追加してそちらはグループ化にします。Where条件の方は表示のチェックを外します。

SELECT D.Code, D.OrderDate, D.Price, R.Code, Count(R.OrderDate) AS
OrderDateのカウント, R.Price FROM Table1 AS D, Table1 AS R WHERE
(((D.Code)=[R].[Code]) AND ((D.OrderDate)<=[R].[OrderDate])) GROUP BY D.Code,
D.OrderDate, D.Price, R.Code, R.Price;

こんなSQLが生成されて、実行結果は下記の通り

D.Code OrderDate D.Price R.Code OrderDateのカウント R.Price
A 2014/01/01 10 A 1 10
A 2014/01/01 10 A 1 20
A 2014/01/01 10 A 1 30
A 2014/01/01 10 A 1 40
A 2014/01/02 20 A 1 20
A 2014/01/02 20 A 1 30
A 2014/01/02 20 A 1 40
A 2014/01/03 30 A 1 30
A 2014/01/03 30 A 1 40
A 2014/01/04 40 A 1 40
B 2014/01/05 11 B 1 11

あれ、R.OrderDateのカウントが全部1で期待した結果になりません。Priceを集計に組み込んでいるためである事が見て取れるので、両方ともクエリから外します。

SELECT D.Code, R.Code, Count(R.OrderDate) AS OrderDateのカウント FROM Table1 AS D,
Table1 AS R WHERE (((D.Code)=[R].[Code]) AND ((D.OrderDate)<=[R].[OrderDate]))
GROUP BY D.Code, R.Code;

D.Code R.Code R.OrderDateのカウント
A A 10
B B 10
C C 10

R.OrderDateはカウントだけでグループ化が外れてしまっているので、グループ化したカウントが行われていません。
R.OrderDateをもう一個加えてグループ化すると、
SELECT D.Code, R.Code, R.OrderDate,
Count(R.OrderDate) AS OrderDateのカウント FROM Table1 AS D, Table1 AS R WHERE
(((D.Code)=[R].[Code]) AND ((D.OrderDate)<=[R].[OrderDate])) GROUP BY D.Code,
R.Code, R.OrderDate;

D.Code R.Code R.OrderDate R.OrderDateのカウント
A A 2014/01/01 1
A A 2014/01/02 2
A A 2014/01/03 3
A A 2014/01/04 4
B B 2014/01/05 1
B B 2014/01/06 2
B B 2014/01/07 3
B B 2014/01/08 4
C C 2014/01/09 1
C C 2014/01/10 2
C C 2014/01/11 3
C C 2014/01/12 4

期待した連番が取得できました。R.Codeは表示させる必要がありませんね。 次に累積を計算してみます
下記のクエリを眺めてみると、
SELECT
D.Code, D.OrderDate, D.Price, R.Code, R.OrderDate, R.Price FROM Table1 AS D,
Table1 AS R WHERE (((D.Code)=[R].[Code]) AND ((D.OrderDate)<=[R].[Orderdate]));

D.Code D.OrderDate D.Price R.Code R.OrderDate R.Price
A 2014/01/01 10 A 2014/01/01 10
A 2014/01/01 10 A 2014/01/02 20
A 2014/01/02 20 A 2014/01/02 20
A 2014/01/01 10 A 2014/01/03 30
A 2014/01/02 20 A 2014/01/03 30
A 2014/01/03 30 A 2014/01/03 30
A 2014/01/01 10 A 2014/01/04 40
A 2014/01/02 20 A 2014/01/04 40
A 2014/01/03 30 A 2014/01/04 40
A 2014/01/04 40 A 2014/01/04 40
B 2014/01/05 11 B 2014/01/05 11

D.Priceも並び替えないと気付きにくいのですが、R.OrderDateでグループ化して D.Priceを加算すると、累計が計算できそうです。
集計クエリに変更して、余分なフィールドを削除し、R.OrderDateでグループ化して D.Priceを加算しようとすると、おなじみのエラーが出ます

[集計] 行の [Where 条件] に指定されているフィールドを表示できません。このフィールドの [表示] チェック ボックスをオフにしてください。クエリの結果にこのフィールドを表示するには、デザイン グリッドに 2 回追加します。クエリの結果に表示されるフィールドでは、[集計] 行 [Where 条件] を指定しないようにし、[表示] チェック ボックスをオンにします。

D.Codeを加えてそちらはグループ化にし、元々の条件を設定しているフィールドは非表示にします。
D.OrderDateはここではグループ化しません。下記の様なSQLになり、累計が計算できました
SELECT D.Code, Sum(D.Price) AS
Priceの合計, R.OrderDate FROM Table1 AS D, Table1 AS R WHERE (((D.Code)=[R].[Code])
AND ((D.OrderDate)<=[R].[Orderdate])) GROUP BY D.Code, R.OrderDate;

Code Priceの合計 OrderDate
A 10 2014/01/01
A 30 2014/01/02
A 60 2014/01/03
A 100 2014/01/04
B 11 2014/01/05
B 32 2014/01/06
B 63 2014/01/07
B 104 2014/01/08
C 12 2014/01/09
C 34 2014/01/10
C 66 2014/01/11
C 108 2014/01/12

こんどは連番と累計を同時に表示させてみます。
連番作成クエリと、累積計算クエリをデザインビューに置き、コードと日付が等しい条件を設定します。

SELECT Qseries.Code, Qseries.OrderDate, Qseries.OrderDateのカウント, Qsum.Priceの合計 FROM
Qseries, Qsum WHERE (((Qseries.Code)=[Qsum].[Code]) AND
((Qseries.OrderDate)=[Qsum].[OrderDate]));

Code OrderDate OrderDateのカウント Priceの合計
A 2014/01/01 1 10
A 2014/01/02 2 30
A 2014/01/03 3 60
A 2014/01/04 4 100
B 2014/01/05 1 11
B 2014/01/06 2 32
B 2014/01/07 3 63
B 2014/01/08 4 104
C 2014/01/09 1 12
C 2014/01/10 2 34
C 2014/01/11 3 66
C 2014/01/12 4 108

サブクエリ化してみます。
この連番作成クエリと、累積計算クエリのところをそれぞれの元のクエリのSQLで置き換えます。

FROM (連番作成クエリSQL) AS Q1, (累積作成クエリSQL) AS Q2

という感じです。
対応するSELECTするフィールドと、WHERE条件のフィールドも、この例ではQ1およびQ2のフィールドに置き換えてやる必要があります。
テキストエディターで置換機能を使って行う方が楽でしょう。

SELECT Q1.Code, Q1.OrderDate, Q1.OrderDateのカウント, Q2.Priceの合計 FROM (SELECT
D.Code, R.OrderDate, Count(R.OrderDate) AS OrderDateのカウント FROM Table1 AS D,
Table1 AS R WHERE (((D.Code)=[R].[Code]) AND ((D.OrderDate)<=[R].[OrderDate]))
GROUP BY D.Code, R.OrderDate) AS Q1, (SELECT D.Code, R.OrderDate, Sum(D.Price)
AS Priceの合計 FROM Table1 AS D, Table1 AS R WHERE (((D.Code)=[R].[Code]) AND
((D.OrderDate)<=[R].[Orderdate])) GROUP BY D.Code, R.OrderDate) AS Q2 WHERE
(((Q1.Code)=[Q2].[Code]) AND ((Q1.OrderDate)=[Q2].[OrderDate]));

結果は先と同様になりました。

Code OrderDate OrderDateのカウント Priceの合計
A 2014/01/01 1 10
A 2014/01/02 2 30
A 2014/01/03 3 60
A 2014/01/04 4 100
B 2014/01/05 1 11
B 2014/01/06 2 32
B 2014/01/07 3 63
B 2014/01/08 4 104
C 2014/01/09 1 12
C 2014/01/10 2 34
C 2014/01/11 3 66
C 2014/01/12 4 108

ところで、連番と累計で与えた条件は同じなので、一つのクエリで単純に求める可能性がありそうです。

連番のクエリ
SELECT D.Code, R.OrderDate, Count(R.OrderDate) AS OrderDateのカウント FROM Table1 AS D, Table1 AS R
WHERE (((D.Code)=[R].[Code]) AND ((D.OrderDate)<=[R].[OrderDate])) GROUP BY
D.Code, R.Code, R.OrderDate;

累積のクエリ
SELECT D.Code, Sum(D.Price) AS Priceの合計,
R.OrderDate FROM Table1 AS D, Table1 AS R WHERE (((D.Code)=[R].[Code]) AND
((D.OrderDate)<=[R].[Orderdate])) GROUP BY D.Code, R.OrderDate;
見比べてみて、こんな風にできました。

SELECT D.Code, Sum(D.Price) AS Priceの合計, R.OrderDate,
Count(R.OrderDate) AS OrderDateのカウント FROM Table1 AS D, Table1 AS R WHERE
(((D.Code)=[R].[Code]) AND ((D.OrderDate)<=[R].[Orderdate])) GROUP BY D.Code,
R.OrderDate;

Code Priceの合計 OrderDate OrderDateのカウント
A 10 2014/01/01 1
A 30 2014/01/02 2
A 60 2014/01/03 3
A 100 2014/01/04 4
B 11 2014/01/05 1
B 32 2014/01/06 2
B 63 2014/01/07 3
B 104 2014/01/08 4
C 12 2014/01/09 1
C 34 2014/01/10 2
C 66 2014/01/11 3
C 108 2014/01/12 4

R.Priceを加えてやると、個々の値段の表示も出来ました

SELECT D.Code, Sum(D.Price) AS Priceの合計,
R.OrderDate, Count(R.OrderDate) AS OrderDateのカウント, R.Price FROM Table1 AS D,
Table1 AS R WHERE (((D.Code)=[R].[Code]) AND ((D.OrderDate)<=[R].[Orderdate]))
GROUP BY D.Code, R.OrderDate, R.Price;

Code Priceの合計 OrderDate OrderDateのカウント Price
A 10 2014/01/01 1 10
A 30 2014/01/02 2 20
A 60 2014/01/03 3 30
A 100 2014/01/04 4 40
B 11 2014/01/05 1 11
B 32 2014/01/06 2 21
B 63 2014/01/07 3 31
B 104 2014/01/08 4 41
C 12 2014/01/09 1 12
C 34 2014/01/10 2 22
C 66 2014/01/11 3 32
C 108 2014/01/12 4 42

デザインビューとSQLビューを見比べながらやっていると、デザインビューにサブクエリもそれなりに再現されるのが興味深いです。 

表を作るのに、下記サイトのお世話になりました。深謝。
http://www.tagindex.com/cgi-lib/excel_simple/markup.cgi