photo credit: TheeErin ??? 0, 2020 via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
引き続きエクセルVBAでAccessデータベースを操作する方法についてお伝えしています。
前回の記事はコチラでした。
HAVING句でグルーピングした結果を絞り込む方法でした。ちょいとマニアックでしたね。
今回は、ちょっと雰囲気を変えまして日付データについてです。
例えば、様々な日付を持つレコードがあったとして
- 特定の年月のデータだけ抽出したい
- 特定の曜日のデータだけ抽出したい
といったことありますよね。
エクセルVBA＋Accessであれば比較的簡単にできます。
ということで、エクセルVBAでAccessデータベースからFormat関数を使って特定の日付のレコードを抽出する方法をお送りします。
お題となるデータベースとプログラム
今回お題とするデータベースは「ga.accdb」というファイル名で、テーブルは以下の「Dailyレポート」です。
当ブログの開設した2/16から現在までのデイリーのアクセス状況のデータになります。
フィールドは
で構成されています。
このデータベースから色々な日付に関する条件でレコードを抽出していきたいと思います。
また使用するVBAプログラムですがベースはこちら。
Sub SelectByDate()
Dim strFileName As String
strFileName = "ga.accdb"
Dim adoCn As Object 'ADOコネクションオブジェクト
Set adoCn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成
adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & strFileName & ";" 'Accessファイルに接続
Dim adoRs As Object 'ADOレコードセットオブジェクト
Set adoRs = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクトを作成
Dim strSQL As String
strSQL = "SELECT * FROM Dailyレポート"
adoRs.Open strSQL, adoCn 'SQLを実行して対象をRecordSetへ
Worksheets("Sheet6").Range("A1").CopyFromRecordset adoRs
adoRs.Close 'レコードセットのクローズ
adoCn.Close 'コネクションのクローズ
Set adoRs = Nothing
Set adoCn = Nothing 'オブジェクトの破棄
End Sub
14行目にある通り、現在のSQL文は全抽出となっていますが、このSQL文を色々と変えることで抽出条件を変更できます。
特定の年月のレコードを抽出する
では、まず特定の年月のレコードだけ抽出をしてみましょう。
WHERE句を使ったSQL文を使えばよさそうですよね。
例えば、2016年1月のデータであればBETWEEN～ANDを使って
SELECT * FROM Dailyレポート WHERE date BETWEEN #2016/01/01# AND #2016/01/31#
でも良いですね。
ですが「あ、でもやっぱり3月を取り出そう！」などと思ったとき、SQL文の修正は若干面倒です。
そんな時にはFormat関数を使えばOKです。
Format関数をSQL文で使う
Format関数はVBAでもお馴染みですが、値を指定した書式に変換する関数です。
Accessデータベースに問い合わせる際のSQL文内でも同様に使用することができるのです。
書き方は
となります。
例えば
SELECT Format(Now(), 'yyyymmdd')
とすると「201612」がA1セルにポツンと出力されます。
Format関数ですが、日付関連について書式に使える文字とその意味を以下にまとめますので、ご活用下さい。
| 文字 | 説明 |
|---|---|
| d |
日を 1 桁または 2 桁の数字で表示します (1 ～ 31)。 |
|
dd |
日を 2 桁の数字で表示します (01 ～ 31)。 |
|
ddd |
曜日を英語の省略形 3 文字で表示します (Sun ～ Sat)。 |
|
w |
曜日を表す数字を表示します (1 ～ 7)。 |
|
m |
月を 1 桁または 2 桁の数字で表示します (1 ～ 12)。 |
|
mm |
月を 2 桁の数字で表示します (01 ～ 12)。 |
|
q |
その日が属する四半期を表示します (1 ～ 4)。 |
|
yy |
西暦の下 2 桁を表示します (01 ～ 99)。 |
|
yyyy |
西暦を 4 桁で表示します (0100 ～ 9999)。 |
|
h |
時を 1 桁または 2 桁の数字で表示します (0 ～ 23)。 |
|
hh |
時を 2 桁の数字で表示します (00 ～ 23)。 |
|
n |
分を 1 桁または 2 桁の数字で表示します (0 ～ 59)。 |
|
nn |
分を 2 桁の数字で表示します (00 ～ 59)。 |
|
s |
秒を 1 桁または 2 桁の数字で表示します (0 ～ 59)。 |
|
ss |
秒を 2 桁の数字で表示します (00 ～ 59)。 |
|
AM/PM |
12 時間制の時刻に、大文字の “AM” または “PM” を付加して表示します。 |
特定の年月のみ抽出するFormat関数によるSQL文
これで、特定の年月のみ抽出するSQL文は作れますよね。
こうなります。
SELECT * FROM Dailyレポート WHERE Format(date, 'yyyymm') = '201601'
このSQL文にてVBAプログラムを回すとこのような結果が得られます。
GROUP BY句と合わせて使う
Format関数ですがGROUP BY句で使用することができます。つまり書式変更した結果でグルーピングができるのです。
ちなみにですが、GROUP BYの使い方については以下記事もご覧くださいね。
年月でグルーピングするSQL文
ではまず、年月でグルーピングしてpageviewsの合計を抽出するSQL文を作ってみましょう。
こちらです。
SELECT Format(date, 'yyyymm'),SUM(pageviews) FROM Dailyレポート GROUP BY Format(date, 'yyyymm')
実行します。
年月ごとのページビュー集計ができました。んー、順調に伸びてますね～。
曜日でグルーピングするSQL文
では、続けて曜日でグルーピングしてみましょう。どの曜日がpageviewsが多いのか…今度は平均で見てみましょう。
SELECT Format(date, 'ddd'),AVG(pageviews) FROM Dailyレポート GROUP BY Format(date, 'ddd') ORDER BY AVG(pageviews) DESC
グルーピングして平均を出しつつ、ORDER BY句で降順にソートをしてみます。
結果はこちら。
曜日ごとの平均ページビューが出せました。火曜日がトップでした。
ORDER BY句の使い方に関しては以下記事もご覧下さいね。
まとめ
エクセルVBAでAccessデータベースからFormat関数を使って特定の日付データを抽出する方法をお伝えしました。
また、GROUP BY句と組み合わせれば、日付に関して様々な切り口でグルーピング集計して抽出することもできましたね。
これは便利です。
なお、Format関数はAccessのSQLに関してのみ使えます、MySQLとかPostgreSQLなどの他のデータベース管理システムではFormat関数は使えませんので、その点ご留意くださいね。（代わりにDATE_FORMAT関数なるものがあるようです。）
さて次回ですが、ちょっと別の話としてトランザクション処理についてお伝えします。
どうぞお楽しみに！
連載目次：エクセルVBAでエクセルからAccessデータベースを操作する
エクセルと相性の良いデータベースとして第一候補として挙がるAccess。エクセルをインターフェース、AccessをデータベースとしてエクセルVBAを使って連携する方法とその様々なテクニックについてお伝えしていきます。- なぜエクセルとAccessデータベースを連携するのが良いのか、またその際の注意点
- エクセルVBAでAccessデータベースと接続する最もシンプルなプログラム
- 【エクセルVBA＆Access連携】SQL文でデータを抽出する最も簡単なプログラム
- 【エクセルVBAでAccess連携】SQLのSELECT,FROM,WHEREによる様々なデータ抽出方法
- 【エクセルVBAでAccess連携】SQLのJOINを使って複数のテーブルを組み合わせてデータを取り出す
- 【エクセルVBAでAccess連携】データベースのテーブルにレコードを追加するシンプルな方法
- エクセルVBAでAccessデータベースに複数のレコードを追加する方法とその実行速度について
- 【エクセルVBAでAccess連携】データベースに特定条件のデータが存在するかどうかを判定する
- エクセルVBAからAccessデータベースのレコードを呼び出して修正して上書き更新する方法
- エクセルVBAでAccessデータベースの複数のレコードをまとめて上書き更新する方法
- エクセルVBAでAccessデータベースの複数のレコードを上書き更新する場合の実行速度について
- エクセルVBAでAccessのデータをORDER BYで並び替えをして取り出す
- エクセルVBAでADODBレコードセットをSortメソッドで並び替えする方法
- エクセルVBAでAccessデータをグループで集計して抽出するGROUP BY句と集計関数の使い方
- エクセルVBAでAccessデータを集合関数による条件で抽出するHAVING句の使い方
- エクセルVBAでAccessデータベースからFormat関数によるSQL文で特定の日付で抽出
- エクセルVBAでのAccessデータベース操作にトランザクション処理を入れる
