![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?8acaa2e)
フィールド(商品コード,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ベストアンサー
- 回答日時:
1商品コードにつき、最大でc0~c4の5つしか収録できなくてもいいということならば、以下の通りできます。
・サブクエリ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
- 回答日時:
#1です。
ちなみに書いたものは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
という結果を返してくれるはずですが、返ってきていますか?
サブクエリ1実行でエラーが出てしまいました。
Access97だからでしょうか・・?
試しに、SQL Serverのクエリで試したところ、サブクエリ1はうまくいきました。
メインクエリも構文をSQL Serverに合わせて一部変更(IIF→CASE WHEN ELSE)し実行したところ、何とか結果を出すことができました。
最終的にはAccessで処理が必要になるため、この結果をTabelに落としそれをAccessにインポートして使おうと思います。
無理かなと半ばあきらめていたので、非常に助かりました。
ありがとうございました。
No.3
- 回答日時:
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にコードが入ってきます。これが複数レコード存在するのですが
同コードでも同列に入ってくるとは限らず、バラバラに入ってきます。それを最終的に入ってきているコードを1レコードにまとめたいのですが。
No.2
- 回答日時:
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, _
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) SUMIFS 一部の条件のどちらかを参照する場合を教えてください。 2 2022/04/26 12:48
- Visual Basic(VBA) vbaマクロについて 【1.csv】をもとに【商品.csv】に有るものを【有り.csv】として名前を 1 2023/05/18 07:58
- Amazon クロネコヤマトの集荷を依頼した場合ネコピットは利用できますか? 1 2023/05/21 19:02
- Excel(エクセル) エクセル 関数について 4 2022/08/05 11:03
- Excel(エクセル) Excel での関数についての質問です。 例えばA列に商品名を、B列に個数をそれぞれ入力しています。 7 2023/05/13 10:51
- Visual Basic(VBA) vbaマクロについて 次のようなマクロを組みたいです。 自分は初心者なので全くわかりません。 詳しく 8 2023/05/18 18:38
- Excel(エクセル) エクセルのVBAにショートカットキーの割り当て 3 2022/07/13 14:19
- その他(Microsoft Office) EXCEL VLOOKUPに関する質問 5 2023/02/08 11:38
- Excel(エクセル) Excelの操作方法を教えてください!頭文字2~8文字で特定の値に返す方法 8 2022/07/14 11:26
- 会計ソフト・業務用ソフト Excelで売上げデータの中の任意の商品の合計を出したい 3 2023/01/18 18:19
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
「マスタ」と「テーブル」の違...
-
Accessでフィールドを比較した...
-
データからの不要コード削除
-
重複するキーから一番古い年月...
-
SQL(Where句)
-
【Access】順位を付けたい
-
遅延制約について
-
集計方法について
-
ACCESSのSQLで、NULLかNULLでな...
-
違いを教えてください
-
ビューで引数を使いたい
-
bcpによる、テーブル→CSVファイ...
-
Access97の文字化けについて
-
コンボボックスで入力したもの...
-
ACCESS2007 フォーム 「バリア...
-
クエリのキャンセルがいつにな...
-
2つの項目が重複するレコード...
-
Accessにインポートしたら並び...
-
アクセスのテーブル作成クエリ...
-
SQLManagementStudioのフィルタ
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
「マスタ」と「テーブル」の違...
-
2つのテーブルから条件に一致...
-
重複するキーから一番古い年月...
-
ACCESS 一つのフィールドに複...
-
行方向のデータを横に並べる
-
PLSQLの識別子エラー
-
VIEWでテーブルの集計結果...
-
Accessでフィールドを比較した...
-
Accessユニオンクエリーで2つ...
-
主キーの変更
-
片方だけ抽出する方法(SQL)
-
ACCESSのコンパイルエラーについて
-
【Access】順位を付けたい
-
[Oracle] UPDATE分の副問い合わ...
-
ACCESS インポート時の重複チ...
-
テーブル値関数経由でのデータ更新
-
Access2002の集計レポート?
-
ACCESS2000でDCount関数の使い方
-
商品コード番号を入力すると商...
-
Inner join と Left joinの明...
おすすめ情報