Access クエリのグループ化

質問

お世話になります。

テーブル
[契約番号]　　　[メーカー]　　　[型番]　　　[定価]
AAA　　　　　　　メーカーA　　　型番01　　\1,000
AAA　　　　　　　メーカーA　　　型番02　　\8,000
AAA　　　　　　　メーカーB　　　型番03　　\5,000
BBB　　　　　　　メーカーA　　　型番01　　\1,000
BBB　　　　　　　メーカーB　　　型番04　　\9,000

だった場合、契約番号単位で[定価]の一番高いレコードだけ抽出させるには
どのようにすればよいでしょうか。

＜結果＞
[契約番号]　　　[メーカー]　　　[型番]　　　[定価]
AAA　　　　　　　メーカーA　　　型番02　　\8,000
BBB　　　　　　　メーカーB　　　型番04　　\9,000

ご教示の程、宜しくお願い致します。

回答No.1

山田 太郎（@f_a_007） · ベストアンサー

ご承知のように

契約番号｜最高契約額
ＡＡＡ＿｜8,000
ＢＢＢ＿｜9,000

という抽出であれば、

SELECT 契約番号, Max(定価) AS 最高契約額
FROM 契約台帳
GROUP BY 契約番号
ORDER BY 契約番号;

というSQL文を実行することで達成されます。そして、それに対応した[メーカー]と[型番]を付加するには、添付図のようなやり方が知られています。

先のSQL文を[契約台帳 クエリ]として登録して、添付図のようにテーブル結合を使う。これによって、次のSQL文が作成されます。

SELECT
　　[契約台帳 クエリ].契約番号,
　　契約台帳.メーカー,
　　契約台帳.型番,
　　[契約台帳 クエリ].最高契約額
FROM [契約台帳 クエリ]
　　INNER JOIN 契約台帳
　　ON ([契約台帳 クエリ].最高契約額 = 契約台帳.定価) AND ([契約台帳 クエリ].契約番号 = 契約台帳.契約番号)

しかし、これでは作りたくもないクエリを作ることになります。そこで、[契約台帳 クエリ]を本来のSQL文に書き換えます。（※出来れば、クエリは一切作らず、利用せずがいいです！）

SELECT
　　G.契約番号,
　　契約台帳.メーカー,
　　契約台帳.型番,
　　G.最高契約額
FROM [SELECT 契約番号, Max(定価) AS 最高契約額 FROM 契約台帳 GROUP BY 契約番号 ORDER BY 契約番号]. AS G
　　INNER JOIN 契約台帳 ON (G.最高契約額=契約台帳.定価) AND (G.契約番号=契約台帳.契約番号);

これの実行結果は添付図の下部に示しています。

お礼

教えて頂いた方法でうまくいきました。

仰る通り、ローカルにクエリを作らずにやるには？と
思ってて。。

なるほど！こうやってFromのところをいじれば、わざわざ
作る必要ないんですね。

大変勉強になりました。ありがとうございました。

回答No.2

山田 太郎（@f_a_007）

【補足】安直な方法！

LEFT JOIN とかサブクエリとかって面倒くさっ。ならば・・・。

SELECT
　　契約台帳.契約番号,
　　Max(契約台帳.定価) AS 最高契約額,
　　DLookup("メーカー", "契約台帳", "契約番号='" & [契約番号] & "' AND 定価=" & [最高契約額]) AS 契約メーカー,
　　DLookup("型番", "契約台帳", "契約番号='" & [契約番号] & "' AND 定価=" & [最高契約額]) AS 契約部品
FROM 契約台帳
GROUP BY 契約台帳.契約番号
ORDER BY 契約台帳.契約番号;

でも可。なお、DLookup()に相当するDBLookup()をADOで自作すれば３倍速で動作します。

SELECT
　　契約台帳.契約番号,
　　Max(契約台帳.定価) AS 最高契約額,
　　DBLookup("SELECT メーカー FROM 契約台帳 WHERE 契約番号='" & [契約番号] & "' AND 定価=" & [最高契約額]) AS 契約メーカー,
　　DBLookup("SELECT 型番 FROM 契約台帳 WHERE 契約番号='" & [契約番号] & "' AND 定価=" & [最高契約額]) AS 契約部品
FROM 契約台帳
GROUP BY 契約台帳.契約番号
ORDER BY 契約台帳.契約番号;

このように　DLookup()やDBLookup()を使えば、LEFT JOIN とかサブクエリ抜きに単一SELECT文でほとんど目的を達成できます。

Public Function DBLookup(ByVal strQuerySQL As String, _
　　　　　　　　　　　　　Optional ByVal ReturnValue = Null) As Variant
On Error GoTo Err_DBLookup
　　Dim DataValue
　　Dim rst　　　　 As ADODB.Recordset

　　Set rst = New ADODB.Recordset
　　With rst
　　　　.Open strQuerySQL, _
　　　　　　　CurrentProject.Connection, _
　　　　　　　adOpenStatic, _
　　　　　　　adLockReadOnly
　　　　If Not .BOF Then
　　　　　　.MoveFirst
　　　　　　DataValue = .Fields(0)
　　　　End If
　　End With
Exit_DBLookup:
On Error Resume Next
　　rst.Close
　　Set rst = Nothing
　　DBLookup = IIf(Len(DataValue & ""), DataValue, ReturnValue)
　　Exit Function
Err_DBLookup:
　　MsgBox "SELECT 文の実行時にエラーが発生しました。(DBLookup)" & Chr$(13) & Chr$(13) & _
　　　　　 "・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　 "・SQL Text=" & strQuerySQL, _
　　　　　 vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_DBLookup
End Function

Public Function CutStr(ByVal Text As String, _
　　　　　　　　　　　　ByVal Separator As String, _
　　　　　　　　　　　　ByVal N As Integer) As String
　　Dim strDatas() As String

　　strDatas = Split("" & Separator & Text, Separator, , 0)
　　CutStr = strDatas(N * Abs(N <= UBound(strDatas)))
End Function

お礼

こちらの回答を見る前に、先の回答にBAを出してしまいました。

＞LEFT JOIN とかサブクエリとかって面倒くさっ。ならば・・・。

わたしとしては先の回答の方がよいかもです。
いったん契約番号と定価（MAX）でグループ化したクエリいったん
作成し（1）、そのクエリとテーブルを紐付けて抽出（2）。

（1）と（2）のSQLをそれぞれコピーして、（2）のFromに
（1）のSQLを貼り付けて、AS xxで名前をつけて・・・って感じで
楽かなと。