※ SQLの窓では、Microsoft.Jet.OLEDB.4.0 使用しているので、最新の Access( .accdb ) への外部参照はできません。( C#、VB、VBScript、PHP で Microsoft.ACE.OLEDB.12.0 を使用すれば .accdb と下位の MDB 両方にアクセスできます )
関連する記事
MDB 接続で、MySQL と SQLServer の表を結合表示する
System.Data.OleDb で select into 構文を使って簡単に Microsoft Access のデータを他の データベースにエクスポートする
SQL文全体
この SQL 文の最も大きな目的は、得意先毎の金額の集計です。その為のベースとなる取引先データは、売上伝票のデータで、伝票番号毎に売り上げた商品と金額が複数記録されています。
取引データには、得意先名や担当者名が無いので他のテーブルから参照する為に結合していますが、group by で指定した取引先コード以外は結果に表示出来ないので( MySQL は可能なようですが、通常は不可 )、max 関数を使用して得意先名と担当者の情報と住所を表示しています。
全て、取引先コードに対して必ず一つの値しか無いのでこのような記述で取り出す事ができます。
select
取引先コード,
max(得意先名) as 得意先名,
sum(金額) as 売上金額,
max(社員コード) as 社員コード,
max(氏名) as 担当者,
left(max(住所１) & max(住所２),30) as 住所
from
([ODBC;Driver={MySQL ODBC 5.3 Unicode Driver};server=localhost;database=lightbox;uid=root;pwd=password].取引データ left outer join [ODBC;Driver={MySQL ODBC 5.3 Unicode Driver};server=localhost;database=lightbox;uid=root;pwd=password].得意先マスタ on 取引データ.取引先コード = 得意先マスタ.得意先コード)
left outer join [ODBC;Driver={SQL Server Native Client 11.0};server=localhost;database=lightbox;uid=sa;pwd=password].社員マスタ on 得意先マスタ.担当者 = 社員マスタ.社員コード
where
Format(取引日付, 'yyyy/MM/dd') between '2005/06/01' and '2005/06/30'
group by 取引先コード
having
max(得意先名) like '%商店%'
Microsoft Access ベースの記述
ここでは、SQL で使える関数は、Microsoft Access のルールに従う必要があるので、left 関数や、文字列の連結と日付との比較の為に Format 関数を使用しています。
このような SQL は、Microsoft の仕様に基づいて実行されて動作するので、from の 前に into [外部参照].表名 と書く事で、ある特定の RDBMS に対するエクスポートを容易に実行する事ができます。
ここでは、解りやすく Excel に出力する書式として以下のようになります
select
取引先コード,
max(得意先名) as 得意先名,
sum(金額) as 売上金額,
max(社員コード) as 社員コード,
max(氏名) as 担当者,
left(max(住所１) & max(住所２),30) as 住所
into [Excel 8.0;DATABASE=c:\tmp\集計関数結果出力.xls].取引先別売上
from
([ODBC;Driver={MySQL ODBC 5.3 Unicode Driver};server=localhost;database=lightbox;uid=root;pwd=password].取引データ left outer join [ODBC;Driver={MySQL ODBC 5.3 Unicode Driver};server=localhost;database=lightbox;uid=root;pwd=password].得意先マスタ on 取引データ.取引先コード = 得意先マスタ.得意先コード)
left outer join [ODBC;Driver={SQL Server Native Client 11.0};server=localhost;database=lightbox;uid=sa;pwd=password].社員マスタ on 得意先マスタ.担当者 = 社員マスタ.社員コード
where
Format(取引日付, 'yyyy/MM/dd') between '2005/06/01' and '2005/06/30'
group by 取引先コード
having
max(得意先名) like '%商店%'
出力先のテーブルが既に存在している場合は、前もって削除しておいて下さい。Excel の場合は、テーブル名はシート名となり、ブックが無い場合は新規に作成されます。
SQL を MDB のビューとして登録
into によるエクスポートが無い SQL 文の場合は ビューとして登録が可能です。トンカチ + V のアイコンで、ビューの名前を入力するダイアログが表示されるので、その時に接続している MDB の中にビューが登録されて、後から容易に実行する事ができます。
SQL を 各 RDBMS のビューとして登録
例えば、MySQL で今回の SQL を記述すると以下のようになります。
select
取引先コード,
得意先名,
sum(金額) as 売上金額,
社員コード,
氏名 as 担当者,
left(concat(max(住所１),max(住所２)),30) as 住所
from
取引データ left outer join 得意先マスタ on 取引先コード = 得意先コード
left outer join 社員マスタ on 担当者 = 社員コード
where
取引日付 between '2005/06/01' and '2005/06/30'
group by 取引先コード
having
得意先名 like '%商店%'
これを MySQL に ビューとして登録してしまえば、SQLの窓では『処理』メニューからエクスポートが各 RDBMS に対して容易に実行できるようになっています。
