質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.60%

Accessでユニークユーザーを月ごとに累計したい

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 737

yu_pe

score 13

実現したいこと

お世話になっております。
Access2016でクエリを作っています。
ユニークユーザーを月ごとに年度内で累計していくクエリを作りたいのですが、
同じ年度内で同じユーザーIDが別の月に出た場合は1カウントとしたいです。
アドバイスお願いいたします!

※「ID」はオートナンバー型です。

【元データ】

ID 見積月 ユーザーID
1 2018/4/1 A
2 2018/4/1 A
3 2018/4/1 B
4 2018/5/1 B
5 2018/5/1 C
6 2018/6/1 A
7 2018/6/1 B
8 2018/6/1 D

【結果】

年度 ユーザー数
2018 2018 4 2
2018 2018 5 3
2018 2018 6 4

試したこと

下記のようなクエリをSQLビューで作ってみたところ、エラーになりました。

■サブクエリ内で親クエリのフィールドを参照できない

select
 Format$(DateAdd("m",-3,A.見積月),"yyyy") AS 年度,
 Format$(A.見積月,"yyyy") AS 年,
  Format$(A.見積月,"m") AS 月,
  (select count([元データ]![ユーザーID])
 from 元データ
   where Format$([元データ]![見積月],"yyyymm") <= Format$(A.見積月,"yyyymm")) as ユーザー数
from 元データ as A
group by
 Format$(DateAdd("m",-3,A.見積月),"yyyy") AS 年度,
 Format$(A.見積月,"yyyy") AS 年,
  Format$(A.見積月,"m") ASorder by
 Format$(DateAdd("m",-3,A.見積月),"yyyy") AS 年度,
 Format$(A.見積月,"yyyy") AS 年,
  Format$(A.見積月,"m") AS 月;

■Join式がサポートされていませんというエラー

select
 format$(dateadd("m",-3,a.見積月),"yyyy") as 年度,
 format$(a.見積月,"yyyy") as 年,
  format$(a.見積月,"m") as 月,
  format$(a.見積月,"yyyymm") as 比較用,
  b.ユーザー数
from 元データ as a
 inner join (
select
 format$(dateadd("m",-3,[元データ]![見積月]),"yyyy") as 年度,
 format$([元データ]![見積月],"yyyy") as 年,
 format$([元データ]![見積月],"m") as 月,
format$([元データ]![見積月],"yyyymm") as 比較用,
  count([元データ]![ユーザーID]) as ユーザー数
from 元データ
group by
  format$(dateadd("m",-3,[元データ]![見積月]),"yyyy"),
  format$([元データ]![見積月],"yyyy"),
   format$([元データ]![見積月],"m"),
   format$([元データ]![見積月],"yyyymm")
) as b 
on (a.年度 = b.年度) and (b.比較用 <= a.比較用);

補足情報(FW/ツールのバージョンなど)

Win7、Access2016です。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 1

checkベストアンサー

+1

■サブクエリ内で親クエリのフィールドを参照できない

そうではなく、group byやorder byにエイリアス(As)が付いているからだと思います。

■Join式がサポートされていませんというエラー

デザインビューだと駄目でしょうけど、SQLビューだと実行できそうですけど。

追記

以下でどうでしょうか。
※年度考慮もれを訂正

select
      年度
    , Format(見積月, "yyyy") As 年
    , Format(見積月, "m") As 月
    , Count(*) As ユーザー数
from (
  select A.年度, A.見積月, B.ユーザーID
  from (
          select Format(DateAdd("m",-3,見積月), "yyyy") AS 年度, 見積月 
          from 元データ 
          group by Format(DateAdd("m",-3,見積月), "yyyy") , 見積月
       ) As A 
       inner join (
         select Format(DateAdd("m",-3,見積月), "yyyy") AS 年度, 見積月, ユーザーID 
         from 元データ 
         group by Format(DateAdd("m",-3,見積月), "yyyy"), 見積月, ユーザーID
       ) As B
       on A.年度=B.年度 and A.見積月>=B.見積月
  group by A.年度, A.見積月, B.ユーザーID
) As C
group by 年度, 見積月


※上記のB部分を別途クエリーにして使用すると、少し記述が簡潔になりますね。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/03/14 16:42

    お忙しいなか早速のご回答ありがとうございます!
    「as」の件、見落としておりました!失礼いたしました。
    修正してみましたが、やはり正しい結果が出ません。
    たびたび申し訳ございませんが、どうしたらよろしいでしょうか。

    ■サブクエリ→「クエリに集計関数の一部として指定された'見積月'が含まれていません」というエラーになります。
    select
    Format$(DateAdd("m",-3,A.見積月),"yyyy") AS 年度,
    Format$(A.見積月,"yyyy") AS 年,
    Format$(A.見積月,"m") AS 月,
    (select
    Count(Format$(B.user_id,"000000"))
    from 元データ as B
    where Format$(B.見積月,"yyyymm") <= Format$(A.見積月,"yyyymm")) as ユーザー数
    from 元データ as A
    group by
    Format$(DateAdd("m",-3,A.見積月),"yyyy"),
    Format$(A.見積月,"yyyy"),
    Format$(A.見積月,"m")
    order by
    Format$(DateAdd("m",-3,A.見積月),"yyyy"),
    Format$(A.見積月,"yyyy"),
    Format$(A.見積月,"m");

    ■inner join → 月ごとのユーザー数のパターンが複数出てしまいました。
    select
    format$(dateadd("m",-3,a.見積月),"yyyy") as 年度,
    format$(a.見積月,"yyyy") as 年,
    format$(a.見積月,"m") as 月,
    format$(a.見積月,"yyyymm") as 比較用,
    b.ユーザー数
    from 元データ as a
    inner join (
    select
    format$(dateadd("m",-3,[元データ]![見積月]),"yyyy") as 年度,
    format$([元データ]![見積月],"yyyy") as 年,
    format$([元データ]![見積月],"m") as 月,
    format$([元データ]![見積月],"yyyymm") as 比較用,
    count([元データ]![user_id]) as ユーザー数
    from 元データ
    group by
    format$(dateadd("m",-3,[元データ]![見積月]),"yyyy"),
    format$([元データ]![見積月],"yyyy"),
    format$([元データ]![見積月],"m"),
    format$([元データ]![見積月],"yyyymm")
    ) as b
    on (format$(dateadd("m",-3,a.見積月),"yyyy") = b.年度) and (b.比較用 <= format$(a.見積月,"yyyymm"));

    キャンセル

  • 2019/03/14 16:47 編集

    サンプル追記されていたので、コメント削除

    キャンセル

  • 2019/03/15 09:08

    うまくいきました!ありがとうございます!

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.60%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る