集計方法について

質問

フィールド（商品コード,c0,c1,c2,c3,c4）に【-元レコード-】ような値（01,02,05など）が入っているとします。
同じ商品コードの場合にc0～c4に入っているコードを【-集計-】のような結果で出したいのですが、Accessのクエリーで集計をすることは可能でしょうか？
可能であればどのような構文になるのか教えていただけませんでしょうか？

【-元レコード-】
――――――――――――――――
商品コード∥c0｜c1｜c2｜c3｜c4｜
――――――――――――――――
0001　　 　∥01｜02｜05｜
――――――――――――――――
0001　　 　∥01｜03｜
――――――――――――――――
0001　　 　∥02｜04｜
――――――――――――――――

【-集計-】
――――――――――――――――
商品コード∥c0｜c1｜c2｜c3｜c4｜
――――――――――――――――
0001　　 　∥01｜02｜03｜04｜05｜
――――――――――――――――

回答No.1

jamshid6 · ベストアンサー

１商品コードにつき、最大でc0～c4の５つしか収録できなくてもいいということならば、以下の通りできます。
・サブクエリ1
　まず、元レコードが集計に全く向いていないので、向いた形にします。
SELECT DISTINCT 商品コード, C
FROM
(SELECT 商品コード, c0 As C FROM TBL1 WHERE c0 IS NOT NULL
UNION ALL
SELECT 商品コード, c1 FROM TBL1 WHERE c1 IS NOT NULL
UNION ALL
SELECT 商品コード, c2 FROM TBL1 WHERE c2 IS NOT NULL
UNION ALL
SELECT 商品コード, c3 FROM TBL1 WHERE c3 IS NOT NULL
UNION ALL
SELECT 商品コード, c4 FROM TBL1 WHERE c4 IS NOT NULL) AS t;
・メインクエリ
　上記サブクエリを使って以下のクエリを書きます。
SELECT 商品コード,
MAX(IIF(SEQ=1,C,'')) AS c0,
MAX(IIF(SEQ=2,C,'')) AS c1,
MAX(IIF(SEQ=3,C,'')) AS c2,
MAX(IIF(SEQ=4,C,'')) AS c3,
MAX(IIF(SEQ=5,C,'')) AS c4
FROM
(SELECT t1.商品コード, t1.C,
(SELECT COUNT(*) FROM サブクエリ1 t2 WHERE t2.商品コード=t1.商品コード AND t2.C<=t1.C) AS SEQ
FROM サブクエリ1 t1) AS t
GROUP BY 商品コード;

もちろん、合わせればクエリ一発でもできますが、わかりにくくなります。

お礼

回答ありがとうございます。
教えていただいた通りに実行したみたのですが、FROM句の構文エラーになってしまい上手くいきません。
これを元に自分なりにもやってみたのですが、エラーになったり理想の形に結果がでません。

回答No.4

jamshid6

＃１です。
ちなみに書いたものはACCESS2000/2007で動作することは確認済です。
どこでエラーになるのか示してもらえると助かります。

「サブクエリ1」は
商品コード　C
--------------------
0001　　　　01
0001　　　　02
0001　　　　03
0001　　　　04
0001　　　　05
という結果を返してくれるはずですが、返ってきていますか？

次にメインクエリのうち、以下の部分は、
SELECT t1.商品コード, t1.C,
(SELECT COUNT(*) FROM サブクエリ1 t2 WHERE t2.商品コード=t1.商品コード AND t2.C<=t1.C) AS SEQ
FROM サブクエリ1 t1

商品コード　C　　　SEQ
-----------------------
0001　　　　01　　　1
0001　　　　02　　　2
0001　　　　03　　　3
0001　　　　04　　　4
0001　　　　05　　　5
という結果を返してくれるはずですが、返ってきていますか？

お礼

サブクエリ１実行でエラーが出てしまいました。
Access97だからでしょうか・・？

試しに、SQL Serverのクエリで試したところ、サブクエリ１はうまくいきました。
メインクエリも構文をSQL Serverに合わせて一部変更（IIF→CASE WHEN ELSE）し実行したところ、何とか結果を出すことができました。

最終的にはAccessで処理が必要になるため、この結果をTabelに落としそれをAccessにインポートして使おうと思います。

無理かなと半ばあきらめていたので、非常に助かりました。
ありがとうございました。

回答No.3

noname#140971

Err_DBSum:
　　 MsgBox "SELECT 文の実行時にエラーが発生しました。(DBSum)" & Chr$(13) & Chr$(13) & _
　　　　　　"・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　　"・SQL Text=" & strQuerySQL, _
　　　　　　vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_DBSum
End Function

お礼

回答ありがとうございます。
ただ、c0～c4はコードであって数量ではありません。
例（【-元レコード-】）の通り商品コード'0001'に対して、c0～c4にコードが入ってきます。これが複数レコード存在するのですが
同コードでも同列に入ってくるとは限らず、バラバラに入ってきます。それを最終的に入ってきているコードを１レコードにまとめたいのですが。

回答No.2

noname#140971

tab1:

[id][商品コード][c0][c1][c2][c3][c4]
_01__0001_________1___1___1___1___1
_02__0001_________1___1___1___1___0
_03__0001_________1___1___1___0___0
_04__0002_________2___2___2___2___2
_05__0002_________2___2___2___0___0

クエリ1:

[商品コード][c0_SUM][c1_SUM][c2_SUM][c3_SUM][c4_SUM]
_0001_____________3_______3_______3_______2_______1
_0002_____________4_______4_______4_______2_______2

SELECT 商品コード,
DBSUM("c0","tab1","商品コード='" & [商品コード] & "'") AS c0_SUM,
DBSUM("c1","tab1","商品コード='" & [商品コード] & "'") AS c1_SUM,
DBSUM("c2","tab1","商品コード='" & [商品コード] & "'") AS c2_SUM,
DBSUM("c3","tab1","商品コード='" & [商品コード] & "'") AS c3_SUM,
DBSUM("c4","tab1","商品コード='" & [商品コード] & "'") AS c4_SUM
FROM tab1
GROUP BY 商品コード;

DBSum関数の類を使えば簡単に集計することができます。
Access のDSum関数を使えると思ったのですが＜循環参照エラー＞が発生しますね。
が、以下のように独自のDSUM関数の代行関数を用意すれば回避出来るようです。

SELECT 商品コード,
(SELECT SUM(c0) FROM tab1 WHERE 商品コード=X.商品コード) AS C0_Sum,
(SELECT SUM(c1) FROM tab1 WHERE 商品コード=X.商品コード) AS C1_Sum,
(SELECT SUM(c2) FROM tab1 WHERE 商品コード=X.商品コード) AS C2_Sum,
(SELECT SUM(c3) FROM tab1 WHERE 商品コード=X.商品コード) AS C3_Sum,
(SELECT SUM(c4) FROM tab1 WHERE 商品コード=X.商品コード) AS C4_Sum
FROM tab1 As X
GROUP BY 商品コード

もちろん、DBSUM関数の実態はSELECT文です。
ですから、直接に記述しても同じ結果を得ます。

Public Function DBSum(ByVal strField As String, _
　　　　　　　　　　　ByVal strTable As String, _
　　　　　　　　　　　Optional strWhere As String = "") As Variant
On Error GoTo Err_DBSum
　　Dim N
　　Dim strQuerySQL As String
　　Dim rst　　　　 As ADODB.Recordset

　　Set rst = New ADODB.Recordset
　　strQuerySQL = "SELECT SUM(" & strField & ") FROM " & strTable
　　If Len(strWhere) > 0 Then
　　　　strQuerySQL = strQuerySQL & " WHERE " & strWhere
　　End If
　　With rst
　　　　 .Open strQuerySQL, _
　　　　　　　 CurrentProject.Connection, _
　　　　　　　 adOpenStatic, _
　　　　　　　 adLockReadOnly
　　　　 If Not .BOF Then
　　　　　　 .MoveFirst
　　　　　　 N = Nz(.Fields(0), 0)
　　　　 End If
　　End With
Exit_DBSum:
On Error Resume Next
　　 rst.Close
　　 Set rst = Nothing
　　 DBSum = N
　　 Exit Function
Err_DBSum:
　　 MsgBox "SELECT 文の実行時にエラーが発生しました。(DBSum)" & Chr$(13) & Chr$(13) & _
　　　　　　"・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　　"・SQL Text=" & strQuerySQL, _