ExcelでADODBで外部結合をしたときに集計結果が倍で表示された原因

ADODBを使ってExcelをデータベースのように扱うのは、SQL文を書くだけで集計出るのでとても便利ですね。

そんなデータベースで集計の指示を出すSQL文で今回始めて

Excel VBAでデータベースの結合処理を行ってみました。

やろうとしたこと

今回ミスったことを簡単に紹介。

①元データ。
f:id:ExcelLover:20200124105458j:plain

②マスターデータ。(突っ込みたい気持ちはわかるが今の段階ではそっとしておいてください。）
f:id:ExcelLover:20200124105509j:plain

①のデータに対して②マスターデータを関連付けて

・コード

・品名

・金額

を１つのデータとして表示しようと思った。

コード

Option Explicit

Sub DoubleSum()
     Dim myCon As ADODB.Connection
     Set myCon = New ADODB.Connection
     myCon.Provider = "Microsoft.ACE.OLEDB.12.0"
     myCon.Properties("Extended Properties") = "Excel 12.0;IMEX=1"
     myCon.Open ThisWorkbook.FullName
     
     Dim myRS As ADODB.Recordset
     Set myRS = New ADODB.Recordset
     
     Dim mySQL As String
     mySQL = "select A.コード,B.品名,sum(金額)  as 合計 from [" & Worksheets(1).Name & "$] as A"
     mySQL = mySQL & " left outer join [" & Worksheets(2).Name & "$] as B on A.コード = B.コード group by A.コード,B.品名"
     myRS.Open mySQL, myCon
     Worksheets(3).Range("A1").CopyFromRecordset myRS
     myRS.Close
     Set myRS = Nothing
     myCon.Close
     Set myCon = Nothing
End Sub

vlookupのようにSQLで関連付けを行うには外部結合「Outer Join句」を使用します。

公式
select テーブル名.カラム名,・・・ from テーブル１ left outer join テーブル２ on テーブル１.カラム名 = テーブル２.カラム名

公式に今回の事例を当てはめてみると

select 元データ.コード,マスター.品名,sum(金額)
from 元データ
left outer join マスターデータ
on 元データ.コード = マスターデータ.コード
group by 元データ.コード,マスター.品名

という風になる。