Excel ブックに対して 所属部署ごとの最大値をGroup Byを利用せずに一行で取得するSQL 文を実行する

Excel のワークシートのデータに対して、所属に応じた給料の最大値をGroup Byを使わずに一行で取得する方法を紹介します。

そのためには DAO(※Data Access Object の略)を利用します。ここで紹介す
る方法を実現するには、「Microsoft Office 14.0 Access database engine Object Library」が参照設定されていることが前提になります。

そのためには DAO(※Data Access Object の略)を利用します。ここでは、DAO を使ったデータベース検索方法を紹介します。

① 「Microsoft Office 16.0 Access database engine Object Library」を参照設定します。

② 参照設定したことで、DAOを利用できます。Excel ブックを用意して、標準モジュールに DAO を利用した次のコードを記載します。

Option Explicit

Public Sub Sample_MaxByDiv_DAO_01()

    ' DAO データベース設定用変数
    Dim db As DAO.Database

    ' DAO レコードセット設定用変数
    Dim rs As DAO.Recordset

    ' 単純なループカウンタ
    Dim lp As Long

    ' 検索結果表示用文字列
    Dim str As String

    ' "C:\Temp\仲間.xlsx"をデータベースとして開く。
    Set db = DBEngine.Workspaces(0).OpenDatabase( _
    "C:\Temp\仲間.xlsx" _
    , False _
    , False _
    , "Excel 12.0;HDR=YES" _
    )

    ' レコードセットを取得する。
    Set rs = db.OpenRecordset( _
    Name:=" SELECT MAX(IIF(所属ID=1,給料,0)), MAX(IIF(所属ID=2,給料,0)) FROM [Sheet1$] " _
    , Type:=dbOpenDynaset _
    )

    ' 対象レコードが１件以上存在するか調べる。
    If rs.EOF Then

    str = "(検索結果：０件)"

    Else

    ' 検索できる全てのレコードを参照する。
    Do Until (rs.EOF)

    ' 全ての検索結果をカンマ区切りで連結する。
    str = str & rs.Fields(0)

    For lp = 2 To rs.Fields.Count
    str = str & "," & rs.Fields(lp - 1)
    Next lp

    str = str & vbLf

    ' 次のレコードに移る。
    rs.MoveNext

    Loop

    End If

    ' カーソルを閉じる。
    rs.Close
    Set rs = Nothing

    ' データベースを閉じる。
    db.Close
    Set db = Nothing

    ' 検索結果を表示する。
    MsgBox str, vbOKOnly

End Sub

③ Sample_MaxByDiv_DAO_01を実行します。Excel ワークシートをデータベースと見なして検索処理が実行されます。