ACCESS VBA EXCEL クエリ 転記 集計 SQL

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

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

これを通常に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列
名前値段個数管理番号　その他の管理番号
雑誌5002A1　A6
写真7003A2　A8,A9,A11
音楽6001A3
ソフト5002A4　A10
写真4001A5
音楽9001A7

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

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

No.2 ベストアンサー 回答者： 30246kiku 回答日時： 2009/02/04 00:56

> 写真 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を差し上げたいと思いますのでお待ちください。

お礼日時：2009/03/11 06:54

No.3 回答者： 30246kiku 回答日時： 2009/02/04 17:05

＃２　修正です

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


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

No.1 回答者： Hardking 回答日時： 2009/02/03 23:50

やり方は何パターンかありますが、

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

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