人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

Accessのテーブルで1行に含まれる複数の列データを集計したいのですが、方法がわかりません。
クロス集計で近いところまでいきましたが、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 |...
...

です。

皆様のお力を貸してください…!
よろしくお願いします。

●質問者: shadow-dragon
●カテゴリ:コンピュータ
○ 状態 :終了
└ 回答数 : 3/3件

▽最新の回答へ

1 ● きゃづみぃ
●30ポイント

テーブルレイアウトの作り方を変更したらいいでしょう。

管理ID | 日付| 内容

AAA | 2010/11 | りんご

AAA | 2010/12 | りんご

AAA | 2011/01 | もも

AAA | 2011/02 | ぶどう

.......

といった感じにすれば 集計しやすくなるでしょう。

または 元のテーブルから このようなテーブルに 抽出しなおすかですね。

◎質問者からの返答

ご回答ありがとうございます。

残念ながらテーブルレイアウトの変更はデータ量が膨大で不可能なのです…。


2 ● うぃんど
●40ポイント ベストアンサー

ワンステップ間にはさむ必要があります

まずは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でつなげるのは厳しそうですね。


3 ● Melodybox
●30ポイント

SQLのみで抽出するには不向きなテーブル設計に思えます。

まず無条件のSQLで抽出した後、AccessVBAなどでレコードごとに集計してはいかがでしょう。

◎質問者からの返答

ご回答ありがとうございます。

やはりVBAですよね...。

集計依頼を受けたデータベースがこのような設計状態で参っていますorz

数百行ぐらいならいいのですが、70000行ほどあり愕然としています。

関連質問

●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ