クロス集計で近いところまでいきましたが、1列しか集計できませんでした…。
◆テーブルレイアウト
管理ID | 2010/11 | 2010/12 | 2011/01 | 2011/02 |........
-------------------------------------------------
AAA | りんご | りんご | もも | ぶどう |........
BBB | もも | りんご | ぶどう | ぶどう |........
CCC | りんご | ぶどう | もも | もも |........
...
◆求めている集計結果
管理ID | りんご | もも | ぶどう |...
-------------------------------------------------
AAA | 2 | 1 | 1 |...
BBB | 1 | 1 | 2 |...
CCC | 1 | 2 | 1 |...
...
です。
皆様のお力を貸してください…!
よろしくお願いします。
ワンステップ間にはさむ必要があります
まずはUNIONクエリで縦に繋げます
SELECT [管理ID], "2010/11" AS 月, [2010/11] AS 果物 FROM テーブル UNION ALL SELECT [管理ID], "2010/12", [2010/12] FROM テーブル UNION ALL 中略 SELECT [管理ID], "2011/09", [2011/09] FROM テーブル
一度に30か40くらいまでしかUNIONで繋げる事はできなかったと記憶していますが、
今日はあまりたくさんは試していません
上記クエリを実行してみて縦に成っていることを確認できれば、
あとはクロス集計クエリを作って実行するだけです
TRANSFORM Count(月) AS 月のカウント SELECT 管理ID FROM 先ほど作ったクエリ GROUP BY 管理ID PIVOT 果物;
追記:
>UNIONでつなげるのは厳しそう
書き方が悪かったですね
「一度に」というのは1つのUNIONでの限界という意味で、
似たようなUNIONクエリを2つ3つと作って、
それらクエリを1つにまとめるUNIONクエリを作り、
クロス集計クエリで用いるなんてことは可能です
70カラムあっても質問文のような命名規則があれば、
最初の1年分で1つのUNIONクエリを作ってしまえば、
あとはコピーして年の部分を書き換えるだけですから、
同じく1年あたりクエリ1つだとして、6年分6つのクエリ・・・
コピペして置換するくらいならあっという間に作れるでしょ?
この方法ならば1つのクエリでは無理なものでも問題なくなるはずです
>列数(日付)が70ほどある
>70000行
さて、前述の方法で横に並んだデータを縦に並べるクエリが出来たとしても、
それらを1つに繋げると7万レコードが70倍の490万レコードに膨れ上がるので、
ここまでになると毎度毎度UNIONではあまり賢いとは言えません
だからといって今回のようなケースでは、
VBAで1レコードずつゴリゴリ処理するよりも、
SQLのほうが手軽で理解しやすいと思いますので、このまま進めます
クエリだけでもかまいませんが、取り扱いやすいように、
作業用のテーブルにしてしまうことを考えましょう
クエリの結果で新しいテーブルを作るクエリの構造は非常に簡単です
SELECT * INTO 新テーブル FROM クエリ
クエリの結果を既存のテーブルに追加するのも簡単です
INSERT INTO 既存テーブル SELECT * FROM クエリ
最初の1年分のクエリで新テーブルを作るためのクエリを作って実行し、
残りの5年分はそれに追加するクエリを作って実行します
ここまでの作業は最初のUNIONクエリ作成から数えても10分程度でできるかもしれません
あとはクロス集計だけです(上に書いたものと特に違わないので省略)
この方法であれば今後データ(カラム)が増えても、
その分だけを追加すれば済むというメリットもあります
ここからは少し話がそれますが、
作業用テーブルをさらにもうひとつ(管理ID,果物,個数)作っておくと、
毎度毎度490万レコードを相手にするよりも格段に楽になるはずです
SELECT 管理ID, 果物, count(*) AS 個数 INTO さらに新しいテーブル FROM 新しいテーブル GROUP BY 管理ID, 果物
こうやって書いている間にMookさんがVBAで回答書いていそうな気もしますけど、
その場合には楽さ加減と、理解しやすさなどで、好きな方法を選択してください
テーブルレイアウトの作り方を変更したらいいでしょう。
管理ID | 日付| 内容
AAA | 2010/11 | りんご
AAA | 2010/12 | りんご
AAA | 2011/01 | もも
AAA | 2011/02 | ぶどう
.......
といった感じにすれば 集計しやすくなるでしょう。
または 元のテーブルから このようなテーブルに 抽出しなおすかですね。
ご回答ありがとうございます。
残念ながらテーブルレイアウトの変更はデータ量が膨大で不可能なのです…。
ワンステップ間にはさむ必要があります
まずはUNIONクエリで縦に繋げます
SELECT [管理ID], "2010/11" AS 月, [2010/11] AS 果物 FROM テーブル UNION ALL SELECT [管理ID], "2010/12", [2010/12] FROM テーブル UNION ALL 中略 SELECT [管理ID], "2011/09", [2011/09] FROM テーブル
一度に30か40くらいまでしかUNIONで繋げる事はできなかったと記憶していますが、
今日はあまりたくさんは試していません
上記クエリを実行してみて縦に成っていることを確認できれば、
あとはクロス集計クエリを作って実行するだけです
TRANSFORM Count(月) AS 月のカウント SELECT 管理ID FROM 先ほど作ったクエリ GROUP BY 管理ID PIVOT 果物;
追記:
>UNIONでつなげるのは厳しそう
書き方が悪かったですね
「一度に」というのは1つのUNIONでの限界という意味で、
似たようなUNIONクエリを2つ3つと作って、
それらクエリを1つにまとめるUNIONクエリを作り、
クロス集計クエリで用いるなんてことは可能です
70カラムあっても質問文のような命名規則があれば、
最初の1年分で1つのUNIONクエリを作ってしまえば、
あとはコピーして年の部分を書き換えるだけですから、
同じく1年あたりクエリ1つだとして、6年分6つのクエリ・・・
コピペして置換するくらいならあっという間に作れるでしょ?
この方法ならば1つのクエリでは無理なものでも問題なくなるはずです
>列数(日付)が70ほどある
>70000行
さて、前述の方法で横に並んだデータを縦に並べるクエリが出来たとしても、
それらを1つに繋げると7万レコードが70倍の490万レコードに膨れ上がるので、
ここまでになると毎度毎度UNIONではあまり賢いとは言えません
だからといって今回のようなケースでは、
VBAで1レコードずつゴリゴリ処理するよりも、
SQLのほうが手軽で理解しやすいと思いますので、このまま進めます
クエリだけでもかまいませんが、取り扱いやすいように、
作業用のテーブルにしてしまうことを考えましょう
クエリの結果で新しいテーブルを作るクエリの構造は非常に簡単です
SELECT * INTO 新テーブル FROM クエリ
クエリの結果を既存のテーブルに追加するのも簡単です
INSERT INTO 既存テーブル SELECT * FROM クエリ
最初の1年分のクエリで新テーブルを作るためのクエリを作って実行し、
残りの5年分はそれに追加するクエリを作って実行します
ここまでの作業は最初のUNIONクエリ作成から数えても10分程度でできるかもしれません
あとはクロス集計だけです(上に書いたものと特に違わないので省略)
この方法であれば今後データ(カラム)が増えても、
その分だけを追加すれば済むというメリットもあります
ここからは少し話がそれますが、
作業用テーブルをさらにもうひとつ(管理ID,果物,個数)作っておくと、
毎度毎度490万レコードを相手にするよりも格段に楽になるはずです
SELECT 管理ID, 果物, count(*) AS 個数 INTO さらに新しいテーブル FROM 新しいテーブル GROUP BY 管理ID, 果物
こうやって書いている間にMookさんがVBAで回答書いていそうな気もしますけど、
その場合には楽さ加減と、理解しやすさなどで、好きな方法を選択してください
ご回答ありがとうございます。
なるほど...ただ、言葉足らずで申し訳なかったんですが列数(日付)が70ほどあるのでUNIONでつなげるのは厳しそうですね。
SQLのみで抽出するには不向きなテーブル設計に思えます。
まず無条件のSQLで抽出した後、AccessVBAなどでレコードごとに集計してはいかがでしょう。
ご回答ありがとうございます。
やはりVBAですよね...。
集計依頼を受けたデータベースがこのような設計状態で参っていますorz
数百行ぐらいならいいのですが、70000行ほどあり愕然としています。
ご回答ありがとうございます。
なるほど...ただ、言葉足らずで申し訳なかったんですが列数(日付)が70ほどあるのでUNIONでつなげるのは厳しそうですね。