9/13/2017

Access。クロス集計クエリで日付を月ごとにグループ化する方法【Format関数】

Access。クロス集計クエリで日付を月ごとにグループ化する方法

<クロス集計クエリ Format関数>

Accessではクエリを使うことによって、
様々なデータを抽出することが出来ますが、
意外と、ちょっとした事が出来ないで困るということを耳にしますので、
今回は、下記のクエリをアレンジしていきながら、
日付を月ごとにグループ化する方法までをご紹介していきます。

デザインビューで確認してみると、

というクエリですが、データシートビューで見ると縦長でわかりにくいので、
縦横のクロス集計クエリに変更してみましょう。

現在は選択クエリなので、クロス集計クエリをクリックしましょう。

すると、デザインビューの設定項目が変更されたことが確認出来ますね。

「集計」行が加わり、グループ化の設定が出来るようになりましたし、
「行列の入れ替え」行も加わりましたね。

ここを設定すると、クロス集計の縦軸と横軸の設定をする事が出来ますね。

今回は、日付を行にして、商品名を列に、金額を値に設定していきます。

集計行の変更箇所は、金額フィールドを合計に変更します。

行列の入れ替え行の変更箇所は、
日付フィールドには、行見出し
商品名フィールドには、列見出し
金額フィールドには、値
を設定しました。

それでは、データシートビューに変えて確認してみましょう。

選択クエリをクロス集計クエリに変更することが出来ましたね。

ところで日付ですが、
年月ごとにまとめて算出したい場合はどうしたらいいのでしょうか?

Excelのピボットテーブルならば、
簡単にグループ化することが出来ますので、
Accessのクエリでも簡単に設定できると思いきや、
残念ながら、そのような機能はないのです。

ではどうしたらいいのかというと、
Format関数を使うことで、年月でグループ化することが出来ます。

それでは、デザインビューに戻して、日付フィールドをアレンジしてみましょう。

日付フィールドのままだと、同じフィールド名が使えませんので、
年月というフィールドを作っていきます。

年月: Format([日付],"yyyy/mm")

とアレンジするだけで、日付を月ごとにグループ化することが出来ます。

"yyyy/mm"は説明するまでもないと思いますが、
yyyyは年を西暦の4桁表示で、mmは月を表しています。

では、データシートビューに切り替えてみましょう。

このように月ごとにグループ化することができましたね。
ところがこのクロス集計クエリ、
月ごとの合計値がないので、追加してみましょう。

デザインビューに切り替えます。

新しいフィールドを作ります。

挿入場所はどこでもOKですが、今回は最後尾に作ります。

金額フィールドで金額を使っていますので、
合計というフィールドで作成する事にします。

フィールドには、合計: 金額
テーブルには、Q受注表
集計には、合計
行列の入れ替えには、行見出し
と設定したら、データシートビューに切り替えてみましょう。

このように、合計値を表示することが出来ましたね。

Excelでは簡単に出来る事も、
Accessでは、ひと工夫する必要もありますので、
知っておくのもいいかもしれませんね。