アクセス フィールド・テーブルの結合について

質問

始めましてアクセス・データベース初心者です。

簡単な質問でしたら申し訳ございませんが
出来る限り分かり易く解答して頂けますと嬉しく思います。

テーブルの中に同じ値のフィールドを持つレコードがあり、
その同一フィールドでまとめて表示したいと思いますが
どのようにすればよいか分かりません。

下記に例を表示致しますのでご参考にして頂ければと思います。

ID　品番　金額
111　AAA　1000円
111　BBB　150円
222　ZZZ　300円

このようなデータを下のようなデータに直したいです。

ID　品番1　金額1　品番2　金額2　品番3　・・・
111　AAA　1000円　BBBB　150円　・・・
222　ZZZ　300円

見にくいと思いますので例の画像も添付させて頂きます。

整理後のフィールドは品番１～品番６・金額１～金額６まで作る予定です。
品番や金額の種類は多数あります。

クロス集計クエリでするのかユニオンクエリというのを使うのかといところまでは調べたのですが、両方イマイチよく分かりません。

かなり困っていますのでどうかお助け下さいませ（泣）。

回答No.2

30246kiku

クロス集計での一例ですが

ID　品番　金額
111　AAA　1000円
111　BBB　150円
222　ZZZ　300円

上記のテーブル名を「TB」、「ID」は数値型、「品番」はテキスト型と仮定します。
また、同一ID内で品番は重複しないものと仮定します。

テーブル「TB」をチョッと加工していきます。

重複した ID=111 の場合、レコード毎に横に表示していきたいので、
その「ID」の中で自分は何番目・・・を追加しておきます。
その順番のフィールド名を「C」と仮定すると、

C ID　品番　金額
1 111　AAA　1000円
2 111　BBB　150円
1 222　ZZZ　300円

になるようにします。
この状態にするには、クエリのSQLビューで、

SELECT DCount("*","TB","ID = " & ID & " AND 品番 <= '" & 品番 & "'") AS C, *
FROM TB;

(今回何番目は、品番の昇順でみてます)
この「C」の値で、横に表示していきます。

また、横に表示する際、解釈するフィールドは基本１つです。
「品番」「金額」の２つを表示させたいので、細工をします。

上記テーブルの各レコードを倍にし、その時に番号を振っておきます。
番号のフィールドを「N」と仮定します。

N C ID　品番　金額
1 1 111　AAA　1000円
2 1 111　AAA　1000円
1 2 111　BBB　150円
2 2 111　BBB　150円
1 1 222　ZZZ　300円
2 1 222　ZZZ　300円

この番号を振りながら各レコードを倍にするには、
フィールド「N」（数値型）だけのテーブル「T_NO」を新設し、1 と 2 だけを登録しておきます。
前述のクエリのもの（以下 Q1 と記述）と、T_NO を直積させます。

SELECT * FROM T_NO, Q1

これで、加工した上記テーブルが出来上がります。
この「N」を何に使うかですが、
N=1 の時には、「品番」を扱いましょう
N=2 の時には、「金額」を扱いましょう
という具合です。

準備が出来たので、このテーブルを使ってクロス集計していきます。
上記テーブルを作りながら、クロス集計する記述は以下
（クエリの記述はこれだけで：上記説明用に記述したものも含まれています）

TRANSFORM First(Choose(N,品番,金額)) AS 値
SELECT ID
FROM T_NO,
(SELECT DCount("*","TB","ID = " & ID & " AND 品番 <= '" & 品番 & "'") AS C, * FROM TB) AS Q1
GROUP BY ID
PIVOT "NO" & C & Choose(N,"_品番","金額");

で、表示結果は

ID　　NO1_品番　　NO1金額　　NO2_品番　　NO2金額
111　　AAA　　　　1000円　　　BBB　　　　150円
222　　ZZZ　　　　300円

のようになります。

PIVOT 部分は、列名を作るところですが、

PIVOT Choose(N,"品番","金額") & C

の様にすると、
ご希望の「品番1」「金額1」のような「列名」になりますが、並び順に難あり。
作られた「列名」が文字列なら、文字列として比較並び替えられます。
サンプル上では、「金額1」「金額2」「品番1」「品番2」の順になります。
クエリをデザインビューにして、「クエリ列見出し」を設定することで、
並びを固定することができます。

以下は、「クエリ列見出し」を使わないでどうにか・・・っていう細工になります。

レコード毎に「品番」「金額」の並びになってもらいたいので、
番号は先頭に付ければ・・・数字で始まるのはヤダから "NO" を付けて・・・

PIVOT "NO" & C & Choose(N,"品番","金額")

レコード毎になったのは良いけど、「品番」「金額」の順が逆・・・
じゃ、"品番" の前にだけ "_" を付けますか・・・

PIVOT "NO" & C & Choose(N,"_品番","金額")

"NO" & C

の部分は今回 1 ～ 6 という事なので、そのままで良いと思いますが、
C が 2 桁とかの場合は、C を Format(C,"00") とかにしないと・・・
「NO1」「NO2」があって「NO11」もあると
「NO1」「NO11」「NO2」の順になるとおもいます・・・・

参考にする/しない等、自己責任でお願いします。

以下のような方法もあるようです
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1317555843

お礼

お礼を申し上げるのが遅くなってしまいまして申し訳ございませんでした。
超多忙につき中々アクセスに手がつかず困っております。

大変親切にご回答頂きまして誠にありがとうございました。
今の仕事が一段落した段階でもう一度トライしてみたいと思います。

回答No.1

piroin654

テーブルの名前をテーブル10とします。
実際に合わせて変更してください。

クエリを三つ作ります。名前はそれぞれQ1、Q2、Q3
とします。

Q1：テーブル10_1　は　テーブル10　を二つデザインビュー
　　で表示させて状態で、一方の名前になります。

SELECT テーブル10.ID, テーブル10.品番, テーブル10.金額, Count(*) AS 番号
FROM テーブル10 INNER JOIN テーブル10 AS テーブル10_1 ON テーブル10.ID = テーブル10_1.ID
WHERE ((([テーブル10].[金額] & [テーブル10].[品番])>=[テーブル10_1].[金額] & [テーブル10_1].[品番]))
GROUP BY テーブル10.ID, テーブル10.品番, テーブル10.金額;

Q2：

SELECT 番号, ID, '品番' AS フィールド, 品番 AS 値 FROM Q1
UNION ALL SELECT 番号, ID, '金額' AS フィールド, 金額 AS 値 FROM Q1;

Q3：

TRANSFORM First(Q2.値) AS 値のカウント
SELECT Q2.ID
FROM Q2
GROUP BY Q2.ID
PIVOT [フィールド] & [番号];

Q3をクリックするとデータが表示されますが、
並びは、品番1、品番2、品番3、・・・、金額1、金額2、金額3・・・
のようになります。データを表示させて並び替えてください。

お礼

お礼を申し上げるのが遅くなってしまいまして申し訳ございませんでした。
超多忙につき中々アクセスに手がつかず困っております。

大変親切にご回答頂きまして誠にありがとうございました。
今の仕事が一段落した段階でもう一度トライしてみたいと思います。