【ACCESS Excel連携】毎月の集計したクエリって意味あるの？
なぜかクエリというものは、増えます。
それは、ACCESSの使い方を知らないからです。
または、覚えるのが面倒だからです。
目次
毎月の集計クエリって意味あるの？
こんにちは。無駄なクエリを僕滅する委員会の伊川(@naonaoke)です。
ACCESSを、我が社でも利用していますが、なぜ、クエリは増え続けるのでしょうか？
カンタンな操作しか、基本書に掲載されていないことが原因でしょう。
ACCESSとExcelの連携となると敷居が高いと思う人も多いようです。
今回は、Excelを利用して、無駄なクエリを僕滅します。
月の集計
4半期毎の集計
月間の個人成績
こんな感じでしょうか？
日本人は、物持ちが良いので、平気で3年前のクエリを残しておきます。
3年前の情報は、普通、毎日の実務では利用しません。
今回の記事で、もう、困ることはなくなります。
このブログはこんな人にお勧め
- ACCESSの中にクエリが沢山ある人
- ExcelとACCESSの連携を勉強している人
- SQLを勉強している人
このブログを、読み終わるころには・・・・
はっきり言いますが、無駄なクエリを30個減らせます。
集計に関するクエリは、作成する必要がなくなります。
問題は、SQLを利用するのですが、SQLの公式を利用すればカンタンです。
ExcelVBAで、SQL文を書く
以前にも、この手の記事は記載しました。
しかし、ユーザーフォーム等、結構、面倒です。
今回は、ユーザーフォームを利用しないで、セルで設定します。
要するに、SQLの公式と、変数の設定だけを理解できればカンタンです。
このようなクエリは不要になります。
SQLの公式に関しては、下記の記事を参照してください。
ExcelでSQL文を書く 作業手順 その1 通常の記載方法
日付で抽出
SQL = “SELECT *FROM T_顧客 WHERE 日付 = #” & Format(TargetDate, “YYYY/MM/DD”) & “#”
期間と名前で抽出
SQL = “SELECT * FROM MT_作業 WHERE 日付 Between #” & Format(始期, “YYYY/MM/DD”) & “# AND #” & Format(終期, “YYYY/MM/DD”) & “# AND ‘” 名前=’”& 名前 & “‘”
こんな感じで記載します。
しかし、これって、結構面倒ですよね。
しかも、結構エラーも発生します。
イヤになります。
しかし、公式を利用すれば、カンタンです。
ExcelでSQL文を書く 作業手順 その2 男だけを転送する
ここに、50件の、なんちゃって個人情報があります。
その中で、男のみを抽出します。
ExcelでSQL文を書く 作業手順 その3 Excelへ転送する
カンタンにできました。
必要な知識は、SQLを記載できるかです。
まず、今回に必要なSQL文は、下記の通りです。
FROM MT_なんちゃって個人情報
WHERE 性別 = ‘男’
このSQL文を、Excelに記載します。
これが完成のコードです。
SQL文のみ抜粋します。
SELECT～
FRPM～
WHERE～
これと変わりませんね。
ExcelでSQL文を書く 作業手順 その4 変数の考え方
ACCESSを利用していると、融通が利かないと思うことが多々ありあす。
型が一致しません。
パラメータが、少なすぎます。
クエリの演算子がありません。
これらのエラーは、大体、ダブルクォーテーションや、シングルクォーテーションの記載に起因します。
今回は、mygenderは、Range(“B1”)に設定しています。
Range(“B1”)には、男を記載しています。
SELECT *
FROM MT_なんちゃって個人情報
WHERE 性別 = ‘男’
上記のSQL文から、男で記載すると、パラメータが少なすぎますとエラーがでます。
なぜか？
‘男’ となっていないからです。
文字型抽出、数値抽出、日付抽出は、それぞれ抽出方法が違います。
このような理屈になっています。
だから、シングルクォーテーションがないと、ACCESSは、文字と認識できないため、型が一致しませんとエラーを返します。
なんで、ダブルクォーテーションがいるのか？
なんで、数値型には、クォーテーションがいらないのか？
それは、SQL文の知識があれば、カンタンに理解できたのです。
ExcelでSQL文を書く 作業手順 その5 期間と名前で抽出する
個人の成績等を確認するのであれば、個人名でもいいですが、
なんちゃって個人情報等では、該当するすべてを抽出したくなります。
そんな時は、ワイルドカードを利用します。
SQL文だけを抜粋します。
日付が、抽出条件になっているので、#（シャープ）が必要です。
また、ADOでワイルドカードを、利用する場合は、*（アスタリスクマーク）ではなく、%（パーセント）になります。
こんな感じで、抽出ができました。
ここがポイント
アンパサンド、クォーテーションで記載するよりも、全然、可読性が向上します。
また、ミスも減ります。
そして、無駄なクエリを作成することなく、Excelへ転送できます。
後は、集計関数を利用して、集計結果をプレゼンすればいいのでないしょうか？
また、集計関数にしても、ある程度、フィルターをかけて、Excelへ転送しているので、
SUMIFS関数、COUNTIFS関数の出番が少なると思います。
まとめ
SQL文を勉強することは、面白く、抽出できたときは最高な気分になります。
また、この技は、実際に、私が、実務で利用していますので、本当に便利な技です。
今回も最後まで読んでいただきありがとうございました。
今回のサンプルファイルは、No304です。
わからない事を延々と考えるのは、無駄です。
- なんで作動かないの？
- もうやだ！VBAなんか嫌い！
- ネットで調べても情報がない！
必ず作動するコードが、ここにあります。
