ACCESS VBA EXCEL クエリ 転記 集計 SQL

質問

ACCESSからEXCELへ転記する際に以下のような一定の操作を行いたいと考えています。

ACCESSの中で以下のようなクエリがあります。
名前 値段 管理番号
雑誌 500 A1
写真 700 A2
音楽 600 A3
ソフト 500 A4
写真 400 A5
雑誌 500 A6
音楽 900 A7
写真 700 A8
写真 700 A9
ソフト 500 A10
写真 700 A11

これを通常にEXCELへ貼り付ける時には以下のようなプログラムをVBAにて作成し実行してます。

With wkb.Worksheets(stSheet)

Do Until rst.EOF
'ここに新たな式を書く
.Cells(3+CntGyo,1)=rst![名前]
.Cells(3+CntGyo,2)=rst![値段]
.Cells(3+CntGyo,3)=rst![管理番号]

CntGyo=CntGyo+1
rst.MoveNext
Loop
End With

ここでEXCELに貼り付ける際に以下のようにしたいと考えております。
A列　　 B列 C列 D列 E列
名前 値段 個数 管理番号　その他の管理番号
雑誌 500 2 A1 　A6
写真 700 3 A2 　A8,A9,A11
音楽 600 1 A3
ソフト 500 2 A4 　A10
写真 400 1 A5
音楽 900 1 A7

名前と値段をグループとして考え、
C列に個数を、D列に管理番号の一番小さい値を示す。
E列にグループ化された管理番号のその他の値を示す。

以上の操作をSQL構文等を使って作れるものなのでしょうか？

回答No.2

30246kiku · ベストアンサー

> 写真 700 3 A2 　A8,A9,A11
は
写真 700 4 A2 　A8,A9,A11
ですよね。

単純に管理番号でソートすると上記は
写真 700 4 A11 　A2,A8,A9 となるので、
頭にスペース付けて６文字に統一して処理している例となります。
（A2 と A11 では、A11 の方が小さい）
（見難くなってますが、RIGHTの右側にある空白は５文字分の空白です）

（テーブル名は「T_T6」としています）

標準モジュールに以下作成

Public Function KanriNoEtc(sName As String, vPrice As Variant) As String
　　Dim rs As New ADODB.Recordset
　　Dim sTmp As String
　　Dim iCount As Long

　　sTmp = ""
　　iCount = 0
　　rs.Source = "SELECT * FROM T_T6 WHERE [名前]='" & sName & "' AND [値段]=" & vPrice & " ORDER BY RIGHT(' ' & [管理番号],6);"
　　rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
　　While (Not rs.EOF)
　　　　If (iCount > 0) Then
　　　　　　sTmp = sTmp & "," & rs("管理番号")
　　　　End If
　　　　iCount = iCount + 1
　　　　rs.MoveNext
　　Wend
　　If (Len(sTmp) > 0) Then
　　　　sTmp = Mid(sTmp, 2)
　　End If
　　KanriNoEtc = sTmp
End Function

※　ADOで書いていますがDAOでもお好きな方を

レコードを得るSQL

SELECT T_T6.名前, T_T6.値段, Count(*) AS 個数, Replace(Min(RIGHT(" " & T_T6.管理番号,6))," ","") AS 管理番号, KanriNoEtc(T_T6.名前,T_T6.値段) AS その他の管理番号
FROM T_T6
GROUP BY T_T6.名前, T_T6.値段
ORDER BY Min(RIGHT(" " & T_T6.管理番号,6));

※　単純に確認するのであれば、クエリのSQLビューに張り付けて
※　データ量が多くなると遅いと思います

組み込む時には、現Excel処理前に行っていたレコード抽出部を上記に変更し

.Cells(3+CntGyo,1)=rst![名前]
.Cells(3+CntGyo,2)=rst![値段]
.Cells(3+CntGyo,3)=rst![個数]
.Cells(3+CntGyo,4)=rst![管理番号]
.Cells(3+CntGyo,5)=rst![その他の管理番号]

とします。

お礼

返事を今まで申し訳ございません。
まだ、動作確認をしておりませんが
後ほど、ptを差し上げたいと思いますのでお待ちください。

回答No.3

30246kiku

＃２　修正です

>　　Wend
>　　If (Len(sTmp) > 0) Then
　↓
　　Wend
　　rs.Close
　　If (Len(sTmp) > 0) Then

※　処理が複雑になっていますが、「管理番号」のデータ桁を揃えておくと簡単になります。
　　RIGHT(" " & T_T6.管理番号,6)　→　T_T6.管理番号　に置き換えれます。
　　また、Replace　も不要になります。

回答No.1

Hardking

やり方は何パターンかありますが、
ＳＱＬ的に一番シンプル方法は、抽出データのソートと
キーブレイク処理で、個数と最小管理番号を取得する方法です。
(1)．抽出データのソート
第一ソート：名前 昇順
第二ソート：値段 昇順
第一ソート：管理番号 昇順
(2)．読み込みデータの名前と値段がともに同一の場合
　　個数カウンターをカウントアップする。

　　読み込みデータの名前と値段がともに同一の場合
　　でそのグループ内で１件の管理番号を最小管理番号とする

　　読み込みデータの名前と値段がともに同一で無いの場合
　　個数カウンターをゼロにする。
上記をＶＢＡマクロでコーディングすればいいです。