重複した複数データを一つのレコードに結合するには？

質問

Ｎｏ｜　製品｜　金額｜
１０｜　鉛筆｜１００｜
１０｜　のり｜　５０｜
２０｜　鉛筆｜１００｜
３０｜絵の具｜３００｜
３０｜ノート｜１５０｜
３０｜　定規｜　８０｜
３０｜ハサミ｜５００｜

　　　　　↓

Ｎｏ｜製品１｜金額１｜製品２｜金額２｜製品３｜金額３｜製品４｜金額４｜製品５｜金額５｜
１０｜　鉛筆｜１００｜　のり｜　５０｜　　　｜　　　｜　　　｜　　　｜　　　｜　　　｜
２０｜　鉛筆｜１００｜　　　｜　　　｜　　　｜　　　｜　　　｜　　　｜　　　｜　　　｜
３０｜絵の具｜３００｜ノート｜１５０｜　定規｜　８０｜ハサミ｜５００｜　　　｜　　　｜

（Ｎｏと金額は半角です。）
のようにデータを結合したいのです。（一つのフィールドに一つの製品、金額）
製品、金額は１から順番に左詰にします。
結果はレポートではなくテーブルで・・・
クエリーで簡単に処理したいのですが、無理ならモジュールで記述してもＯＫです。

使用しているデータベースソフトはAccess2000です。

実際凄く困っています。できれば早急にご返事いただければ幸いです。
よろしくm(._.)mお願いします。

回答No.4

klub · ベストアンサー

モジュールでやるとこんな感じです。
DoCmd.RunSQL "DELETE T_結合.* FROM T_結合;"

Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Dim RS2 As ADODB.Recordset
Dim 退避ナンバー As Long
Dim カウント As Integer

Set CN = CurrentProject.Connection
Set RS = New ADODB.Recordset
Set RS2 = New ADODB.Recordset

RS.CursorLocation = adUseClient

RS.Open "T_製品", CN, adOpenKeyset, adLockOptimistic
RS2.Open "T_結合", CN, adOpenKeyset, adLockOptimistic

RS.Sort = "ナンバー ASC"

RS.MoveFirst
カウント = 1

Do Until RS.EOF

退避ナンバー = RS!ナンバー

RS2.AddNew
RS2!ナンバー = RS!ナンバー

Do Until 退避ナンバー <> RS!ナンバー

RS2("製品" & カウント) = RS!製品
RS2("金額" & カウント) = RS!金額
カウント = カウント + 1
RS.MoveNext
If RS.EOF Then Exit Do
Loop

RS2.Update
カウント = 1
Loop

RS.Close
RS2.Close
CN.Close

End Sub

テーブルは下記状況で作成しました。
・T_製品
　ナンバー　数値型　
　製品　　　テキスト型
　金額　　　通貨型
・T_結合
　ナンバー　数値型　
　製品1 　　テキスト型
　金額1　 　通貨型
　　　　　：
　　　　　：
　製品10 　　テキスト型
　金額10　 　通貨型

上記モジュールを何かのイベントで動かしてください。
（クリック時とか）
モジュール以外で自動で行うのは結構大変かも。

お礼

ありがとうございました。うまくでき、今後の参考にもなり勉強にもなりました。

>Set CN = CurrentProject.Connection
>Set RS = New ADODB.Recordset
>Set RS2 = New ADODB.Recordset

>RS.CursorLocation = adUseClient

>RS.Open "T_製品", CN, adOpenKeyset, adLockOptimistic
>RS2.Open "T_結合", CN, adOpenKeyset, adLockOptimistic

>RS.Sort = "ナンバー ASC"

>RS.MoveFirst
>カウント = 1

>Do Until RS.EOF

　　　↑
ちょっと意味がわからないのですが・・・

回答No.5

stork

長いですが動作確認済みです、以下のクエリでどうでしょうか。
--------------------------------------------
select [No]
,max(製1) as 製品1
,max(金1) as 金額1
,max(製2) as 製品2
,max(金2) as 金額2
,max(製3) as 製品3
,max(金3) as 金額3
,max(製4) as 製品4
,max(金4) as 金額4
,max(製5) as 製品5
,max(金5) as 金額5
,max(製6) as 製品6
,max(金6) as 金額6
,max(製7) as 製品7
,max(金7) as 金額7
,max(製8) as 製品8
,max(金8) as 金額8
,max(製9) as 製品9
,max(金9) as 金額9
,max(製10) as 製品10
,max(金10) as 金額10

into [結果テーブル]
from (select [No]
,iif([順序]=1,[製品],null) as 製1
,iif([順序]=1,[金額],null) as 金1
,iif([順序]=2,[製品],null) as 製2
,iif([順序]=2,[金額],null) as 金2
,iif([順序]=3,[製品],null) as 製3
,iif([順序]=3,[金額],null) as 金3
,iif([順序]=4,[製品],null) as 製4
,iif([順序]=4,[金額],null) as 金4
,iif([順序]=5,[製品],null) as 製5
,iif([順序]=5,[金額],null) as 金5
,iif([順序]=6,[製品],null) as 製6
,iif([順序]=6,[金額],null) as 金6
,iif([順序]=7,[製品],null) as 製7
,iif([順序]=7,[金額],null) as 金7
,iif([順序]=8,[製品],null) as 製8
,iif([順序]=8,[金額],null) as 金8
,iif([順序]=9,[製品],null) as 製9
,iif([順序]=9,[金額],null) as 金9
,iif([順序]=10,[製品],null) as 製10
,iif([順序]=10,[金額],null) as 金10
from (SELECT (select count(*) from tableA as Sub where main.no = sub.no and main.製品>sub.製品)+1 AS 順序
,*
FROM tableA AS Main) as tableB) as tableC
group by [No]
-----------------------------------------
３つのクエリを無理やりひとつにしています。
レスポンスが悪い場合は適宜分割してください。
結果の列数は製品１０までの固定です。列が可変の場合はAccessのクエリでは無理ですね（たぶん）。

お礼

ありがとうございました。クエリでもこんなやり方ができるのですね。
いろいろと教えていただき大変助かりました。

回答No.3

stork

再びstorkです。遅くなってすみません。

テーブルが１００件程度で、結果の列数も固定だと言うことで、テーブルに製品番号カラムを追加し、番号を手作業で振って、結果テーブルにinsertしていくのはいかかがでしょうか。
順を追って説明すると、
１．テーブルに列を追加。列名：製品番号
２．列：製品番号に番号を振る。（製品１なら１）
３．結果セットを入れるテーブルを作る
４．結果テーブルにナンバーを振っておく
５．更新クエリを使用して結果セットに値を入れる。
　ＳＱＬはこんな感じ
　update tableA inner join tableB
on tableA.No = tableB.No
set tableB.製品１ = tableA.製品
tableB.金額１ = tableA.金額
　where tableA.製品番号 = 1
これを番号を変えて１０回実行する。

面倒臭いですかね.....。
モジュールを記述するより簡単だと思いますが。

お礼

解答ありがとうございます。
番号を手作業で振るということですが、処理を自動化にしたいので自動的に番号を振る方法はないですか？（すいません私の説明が不足していたようで・・・）

回答No.2

stork

お力になれるかどうか分かりませんが、考えてみますので、以下のことを教えて下さい。
　１．テーブルの主キー
　２．テーブルのおおよその件数
　３．結果例の列は、製品５まであるが、固定か可変か。また、おおよその列数はどれくらいか

お礼

ありがとうございます。
　1.【NO】です。
　2.件数は決まっていないのではっきりとした件は言えませんが100件未満
　3.列数は10ぐらいで固定です。

宜しくお願いします。

回答No.1

msystem

Accessならばクロス集計クエリというのがあります。
これなら、希望されていることができると思います。
結果はテーブルでというのが気になりますが、参照ならクエリでは・・・
テーブルにしたいのであれば、それにさらに、テーブルを作成するモジュールを組み合わせ、追加クエリでということになると思います。

お礼

早速のご回答ありがとうございます。
ですが、以前クロス集計したらうまくできませんでした。
どの項目を行見出し、列見出し、値と指定するか詳しく教えてくださると助かります。宜しくお願いします。