Accessで期間指定の売上合計と順位指定のクエリ

Question

Accessのクエリの質問です。

(1)期間を指定した、顧客の売上を合計。
(2)その順位を出し、1位～100位や250位などの順位を指定した抽出をしたいです。

(1)期間指定の売上合計はできています。
(2)の順位をDCountでしましたが、金額(集計:合計)にしているためか構文エラーがでます。

よろしくお願い致します。

------------------------------------------------
2つのテーブルから、Q_売上(クエリ)を作成しました。

tbl顧客
顧客番号
顧客名

tbl販売
販売番号
日付
顧客番号
金額

Q_売上
日付(集計:Where条件)
顧客番号(集計:グループ化)
顧客名(集計:グループ化)
金額(集計:合計)

日付の抽出条件は、
Between [開始] And [終了]
としています。
------------------------------------------------

piroin654 · Accepted Answer

No2です。訂正があります。フィールド　　│　順位:顧客番号　　│　金額の合計　　　　│　顧客番号テーブル　　　│　Q_売上_1　　　　│　Q_売上_1　　　　　　│　Q_売上_1 集計　　　　　│　カウント　　　　│　Where条件　　　　　│　Where条件並べ替え　　　│ 表示　　　　　│　　チェックあり　│　チェックなし　　　　│　チェックなし抽出条件　　　│>[抽出順位]　　│>[Q_売上]![金額の合計]　│ または　　　　│　　　　　　　　│　　　　　　　　　　　　│[Q_売上]![顧客番号] のところで、抽出条件　　　│>[抽出順位]　　│>[Q_売上]![金額の合計]　│ において、>[抽出順位]　の不等号の向きが反対で、かつ不等号が抜けていました。　　　　<=[抽出順位] のように、してください。したがって、フィールド　　│　順位:顧客番号　　│　金額の合計　　　　│　顧客番号テーブル　　　│　Q_売上_1　　　　│　Q_売上_1　　　　　　│　Q_売上_1 集計　　　　　│　カウント　　　　│　Where条件　　　　　│　Where条件並べ替え　　　│ 表示　　　　　│　　チェックあり　│　チェックなし　　　　│　チェックなし抽出条件　　　│<=[抽出順位]　　│>[Q_売上]![金額の合計]　│ または　　　　│　　　　　　　　│　　　　　　　　　　　　│[Q_売上]![顧客番号] となります。欠番があった場合はNo2の説明の通りです。したがって、クエリのSQLビューも以下になります。 SELECT Q_売上.顧客番号, Q_売上.顧客名, Q_売上.金額の合計, Count(Q_売上_1.顧客番号) AS 順位 FROM Q_売上, Q_売上 AS Q_売上_1 WHERE (((Q_売上_1.金額の合計)>[Q_売上]![金額の合計])) OR (((Q_売上_1.顧客番号)=[Q_売上]![顧客番号])) GROUP BY Q_売上.顧客番号, Q_売上.顧客名, Q_売上.金額の合計 HAVING (((Count(Q_売上_1.顧客番号))<=[抽出順位])); また、説明の途中の　　　>[抽出順位] のところも、　　　<=[抽出順位] におきかえてください。 Between [最初の順位] And [最終順位] とする場合は、変更はありません。失礼しました。たぶん、もう間違いはないと思いますが。

piroin654 · Answer

新しいクエリに、テーブルとして[Q_売上]を二つ追加します。
一つは、[Q_売上]、もう一つは[Q_売上_1]という名前に
設定されます。

クエリのフィールドに、顧客番号、顧客名、金額の合計の
フィールドを設定し、Σを押して集計を設定します。

空いているフィールドに次のように設定します。
なお、金額の合計、顧客番号は[Q_売上_1]のフィールドであって、
[Q売上]のフィールドではないことに注意をしてください。

フィールド　　│　順位:顧客番号　　│　金額の合計　　　　│　顧客番号
テーブル　　　│　Q_売上_1　　　　│　Q_売上_1　　　　　　│　Q_売上_1
集計　　　　　│　カウント　　　　│　Where条件　　　　　│　Where条件
並べ替え　　　│
表示　　　　　│　　チェックあり　│　チェックなし　　　　│　チェックなし
抽出条件　　　│>[抽出順位]　　│>[Q_売上]![金額の合計]　│
または　　　　│　　　　　　　　│　　　　　　　　　　　　│[Q_売上]![顧客番号]

気をつけるのは顧客番号の抽出条件が金額の合計とずれていて
[または]のところに設定されていることです。
また、同一順位が複数ある場合には次の順位が欠番に
なります。たとえば1位が3人いた場合は、次の順位は
4位になります。クエリのSQLビューでは、

SELECT Q_売上.顧客番号, Q_売上.顧客名, Q_売上.金額の合計, Count(Q_売上_1.顧客番号) AS 順位
FROM Q_売上, Q_売上 AS Q_売上_1
WHERE (((Q_売上_1.金額の合計)>[Q_売上]![金額の合計])) OR (((Q_売上_1.顧客番号)=[Q_売上]![顧客番号]))
GROUP BY Q_売上.顧客番号, Q_売上.顧客名, Q_売上.金額の合計
HAVING (((Count(Q_売上_1.顧客番号))>[抽出順位]));

のように表示されるはずです。

また、[順位:顧客番号]のフィールドの抽出条件に
　　　>[抽出順位]
としていますが、これだとたとえばパラメータの入力で
100とすれば1位から100までが表示されますが、ここを
　　　Between [最初の順位] And [最終順位]
にすれば、任意の順位間を表示することができます。
つまり、パラメータの入力で100と200をそれぞれ
入力すると100位から200位が表示されます。
1と1を入力すると1位が表示されます。ただし、たとえば50位を表示する場合、
パラメータで50と50を入力しますが、
たまたま、49位が複数いた場合は、50位は欠番になるので50位は表示されない
ことになります。同様に、1位から50位まで表示する場合、上記の場合に
1位から49位までが表示されることになります。欠番が生じる場合は、
[順位:顧客番号]のフィールドの抽出条件に
　　　>[抽出順位]
とした場合も同様です。
クエリのSQLビューでは

SELECT Q_売上.顧客番号, Q_売上.顧客名, Q_売上.金額の合計, Count(Q_売上_1.顧客番号) AS 順位
FROM Q_売上, Q_売上 AS Q_売上_1
WHERE (((Q_売上_1.金額の合計)>[Q_売上]![金額の合計])) OR (((Q_売上_1.顧客番号)=[Q_売上]![顧客番号]))
GROUP BY Q_売上.顧客番号, Q_売上.顧客名, Q_売上.金額の合計
HAVING (((Count(Q_売上_1.顧客番号)) Between [最初の順位] And [最終の順位]));

のように、表示されるはずです。

nda23 · Answer

順位とは何か？
自分と比べて金額の多い人が何人いるか、で
決まりますね。つまり、自分より金額の多い
レコードの個数が順位です。

クエリのSQLは以下のようになります。
SELECT
 A.顧客番号,
,MAX(A.顧客名) AS 名前
,MAX(A.金額) AS 売上高
,COUNT(B.顧客番号) AS 順位
FROM Q_売上 AS A
LEFT JOIN Q_売上 AS B
ON A.金額<=B.金額
GROUP BY A.顧客番号
ORDER BY COUNT(B.顧客番号)

上記はAが主体です。Aのある人から見て、
金額が同じ以上の人のカウントが順位です。
トップなら、B側で一致するレコードは自分自身の
１レコードだけなので、COUNTは1です。
2番目なら1番の人と、自分で合計は2です。
同じ金額の人がいると、順位は抜け番になります。
同額トップが２人いると、1位はなく、2位が2人
となります。

SQLの結合式が等号でないため、ビルダでは
作れません。SQLビューで直接SQLを書き込み
ます。

Accessで期間指定の売上合計と順位指定のクエリ

No2です。

新しいクエリに、テーブルとして[Q_売上]を二つ追加します。

順位とは何か？

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング