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 |...
...

です。

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

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2011/10/20 15:44:01
  • 終了:2011/10/21 09:10:44

ベストアンサー

id:windofjuly No.2

うぃんど回答回数2625ベストアンサー獲得回数11492011/10/20 16:15:54

ポイント40pt

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

 

まずは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:shadow-dragon

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

なるほど...ただ、言葉足らずで申し訳なかったんですが列数(日付)が70ほどあるのでUNIONでつなげるのは厳しそうですね。

2011/10/20 23:35:29

その他の回答(2件)

id:taknt No.1

きゃづみぃ回答回数13538ベストアンサー獲得回数11982011/10/20 15:57:26

ポイント30pt

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

管理ID | 日付| 内容

AAA | 2010/11 | りんご

AAA | 2010/12 | りんご

AAA | 2011/01 | もも

AAA | 2011/02 | ぶどう

.......

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

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

id:shadow-dragon

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

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

2011/10/20 16:12:27
id:windofjuly No.2

うぃんど回答回数2625ベストアンサー獲得回数11492011/10/20 16:15:54ここでベストアンサー

ポイント40pt

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

 

まずは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:shadow-dragon

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

なるほど...ただ、言葉足らずで申し訳なかったんですが列数(日付)が70ほどあるのでUNIONでつなげるのは厳しそうですね。

2011/10/20 23:35:29
id:Melodybox No.3

Melodybox回答回数4ベストアンサー獲得回数02011/10/20 22:49:30

ポイント30pt

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

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

id:shadow-dragon

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

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

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

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

2011/10/20 23:38:09
  • id:Mook
    データ集計には向かないテーブル設計ですね。

    >残念ながらテーブルレイアウトの変更はデータ量が膨大で不可能なのです…。
    データ量が多いことは設計変更ができない理由にはならないと思いますよ。
    どういうフィールドがいくつあるかを提示されれば、変換のためのクエリも
    回答できると思いますが、どういうものなのでしょうか。
  • id:shadow-dragon
    Mookさま
    ご助言ありがとうございます。
    フィールドとしては ユニークなID列が1列、日付列(YYYYMM)が70列ほどあります。
    1レコードにID、ID毎の日付断面での状態(A・B・C・D・E・F)を記録しているようです。
    エクセルにて行列入れ替えとピボットテーブルも検討しましたが、行数は全件で70000件ほどあるのとOffice2000のため65536行以上処理できず困惑しています....
  • id:windofjuly
    うぃんど 2011/10/21 01:10:56
    返信を見てNo.2に大幅加筆しました
     
    23:47のコメントは見逃していましたが特に変更は必要なさそうに思えますので、とりあえず
  • id:taknt
    オフィス2010だったら 7万行でも 大丈夫です。
    マイクロソフトのホームページから 期間限定だけの体験版が ダウンロードできますので、それで試してみたらいかがでしょうか?
    って 2ヶ月ぐらいしか使えませんが。
  • id:shadow-dragon
    皆様ご回答いただきありがとうございました!!
    windofjulyさまよりいただいたご助言でいけそうです。
    本当にありがとうございました。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません