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

質問

Accessのクエリの質問です。

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

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

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

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

tbl顧客
顧客番号
顧客名

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

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

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

回答No.3

piroin654 · ベストアンサー

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 様

２回も、回答をありがとうございます。

前回も回答を下さった方ですね。
お世話をかけました。

こちらの方法は、質問する前にネットにて調べて試していました。
私が試した時は、ならなかったので、こちらに質問しました。

フィールドへの書き込み方が違っていました。

親切に書いてあったので、手順どおりに進めたら完成しました。

Between [最初の順位] And [最終順位]

でも出したい時がありますので、とても助かります。

回答No.2

piroin654

新しいクエリに、テーブルとして[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 [最終の順位]));

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

回答No.1

nda23

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

クエリの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を書き込み
ます。

お礼

nda23　様

回答ありがとうございます。

フォームの入力で回答を下さったかたですね。

SQL構文が似ています。
私が前回の回答を理解していれば、解決したかもしれません。
お世話をかけました。

1位から順位を出すときは、こちらの構文が短くてすっきりしています。

piroin654 様 はビルダで作成、nda23　様　は直接書く。

とても参考になります。