【Access】取引データから１ヶ月・社員ごとに売上件数・金額を集計するクエリ

Accessは大量のレコードを管理するのに便利ですが、分析のために集計をする方法をあまりよく知りませんでした。

Excelであればピボットテーブルでちょちょいとできる集計ですが、Accessではどうやるのかなってことで調べました。

内容

テーブル「T_取引テーブル」に取引データを集めました。ID、取引日、社員コード、取引先コード、売上があります。

テーブル「M_社員マスタ」には社員コードと社員名があります。

もちろん中身はダミーです。少し前まではWeb上に作成サービスがあってそれを使ってましたが、最近は生成AIにテストデータを作ってもらってます。思い通りのものが作れて便利ですよ。

あとテーブル「M_取引先マスタ」も用意しましたが、今回は使いませんでしたので割愛です。

このようなクエリを作ります。

１ヶ月と社員ごとに件数と売上の合計を表示するクエリ「Q_売上集計」です。

新しい月から並ぶようにしています。

SQLモードでクエリ作成

今回はSQLから作りました。理由はというと、最近はWebで調べるよりも生成AIを使って作り方を調べることの方が多く、そうするとSQLで教えてくれることが多いからです。便利なんだけど頭が悪くなりそうで怖いですね。

SQLモードで開き、下記のように入力します。

SELECT
    Format(T.取引日, "yyyy/mm") AS 年月,
    T.社員コード AS 社員コード,
    M.社員名 AS 社員名,
    Count(*) AS 件数,
    Sum(T.売上) AS 売上合計
FROM
    T_取引テーブル AS T
    INNER JOIN M_社員マスタ AS M ON T.社員コード = M.社員コード
GROUP BY
    Format(T.取引日, "yyyy/mm"),
    T.社員コード,
    M.社員名
ORDER BY
    Format(T.取引日, "yyyy/mm") DESC,
    T.社員コード;

取引日はyyyy/mm/ddの書式で記録されており、これを月ごとに集計する場合は、Format関数でyyyy/mmの書式に変えてやると簡単です。

件数はCount(*)、合計はSum(対象フィールド)で集計できます。

別のテーブルとリレーションを組む場合はINNER JOINで行います。ONの後ろでどのフィールドでリレーションを組むかを指定しています。FROM句において、記述を簡略化するためにT_取引テーブルをT、M_社員マスタをMと定義しています。

GROUP BYでどの項目で集計をするかを指定します。T.社員コードとM.社員名はどちらか１つでも良さそうな気がしますが、両方ないとだめっぽいです。

ORDER BYは並び替えです。取引日は降順、社員コードは昇順で並べます。

デザインモードの場合

SQLで設定した後にデザインモードではどのように表示されるか確認してみました。

これぐらいの内容であればSQLでないとできないのかなと思いましたが、デザインモードでも簡単に設定できそうですね。