ACCESSを活用して商品変動を追いたい（別）

以下受注情報（テーブル）があります。


受付日　　氏名　　　　電話番号　　商品コード　商品名　数量　価格
2014/4/1　　山田花子　　12345678　55555　魔法瓶　　1　　　　2980
2014/4/2　　大島太陽　　34567890　55555　魔法瓶　　1　　　　2980
2014/5/1　　山田花子　　12345678　33333　御茶碗　　1　　　　1280
2014/5/14　　大島太陽　　34567890　55555　魔法瓶　　2　　　　5960
2014/7/15　　山田花子　　12345678　22222　洗面器　　1　　　　　980
2014/8/19　　佐藤一郎　　56789870　11111　お箸　　1　　　　　580


上記について、氏名、電話番号が同じお客様の場合で、
商品コード、商品名が前のリストから変わったリストのみ

クエリー（ＳＱＬ？）で、以下のように抽出することは難しいでしょうか。
（変わったという判断は直前を見る）


受付日　切り替え　 氏名 　電話番号　　　商品コード　商品名　数量　　価格
2014/4/1　　　　山田花子　12345678　　　55555　　　　魔法瓶　1　　2980
2014/5/1　　30　　　　山田花子　12345678　　　33333　　　　御茶碗　1　　1280
2014/7/15　　75　　　　山田花子　12345678　　　22222　　　　洗面器　1　　　980


切り替え：前に注文した日付からの経過（日）を反映したい。

またテーブルの列は追加（集計ロジックに関係しないただの列）されていくこともあり、テーブルについては列全部をクエリーでは自動反映するとしたい。
（わざわざその都度、追加列を指定したくない）


お手数ですがご教授いただければ幸いです（願）。

No.1 回答者： 30246kiku 回答日時： 2014/10/08 21:00

どこまで考えられての質問になりますか？

考えていた所までで良いので、提示してもらえませんか？

以下のように 6/1 に 以前と同じものがあった場合、
7/15 時点の「切り替え」の値は何になるのでしょうか？

> 前のリストから変わったリストのみ
の意味が良く分かりません
以下状況で、山田花子 に絞った表示はどうなりますか？

・連続した「御茶碗」だけを除外するのか
・「魔法瓶」は前のリストにあるため、6/1 は全部除外するのか


受付日　　氏名　　　　電話番号　　商品コード　商品名　数量　価格
2014/4/1　　山田花子　　12345678　55555　魔法瓶　　1　　　　2980
2014/4/2　　大島太陽　　34567890　55555　魔法瓶　　1　　　　2980
2014/5/1　　山田花子　　12345678　33333　御茶碗　　1　　　　1280
2014/5/14　　大島太陽　　34567890　55555　魔法瓶　　2　　　　5960
2014/6/1　　山田花子　　12345678　33333　御茶碗　　2　　　　2560
2014/6/1　　山田花子　　12345678　55555　魔法瓶　　1　　　　2980
2014/7/15　　山田花子　　12345678　22222　洗面器　　1　　　　　980
2014/8/19　　佐藤一郎　　56789870　11111　お箸　　1　　　　　580


なお、できるかもしれない SQL を提示したとして、
それを、どの様にして確認されているのでしょうか

http://oshiete.goo.ne.jp/qa/8760259.html
は、本当に動かないのでしょうか
これの回答で、質問者さんのレベルを計らせてもらいます
（噛み砕いた説明が必要なのかを判断する為）

この回答への補足

以下のように 6/1 に 以前と同じものがあった場合、
7/15 時点の「切り替え」の値は何になるのでしょうか？


＞2014/7/15　　山田花子　　12345678　22222　洗面器　　1　　　　　980

上記としたい。





前のリストから変わったリストのみ
の意味が良く分かりません
以下状況で、山田花子 に絞った表示はどうなりますか？

・連続した「御茶碗」だけを除外するのか
・「魔法瓶」は前のリストにあるため、6/1 は全部除外するのか

＞

受付日　　氏名　　　　電話番号　　商品コード　商品名　数量　価格
2014/4/1　　山田花子　　12345678　55555　魔法瓶　　1　　　　2980
2014/5/1　　山田花子　　12345678　33333　御茶碗　　1　　　　1280
2014/6/1　　山田花子　　12345678　55555　魔法瓶　　1　　　　2980
2014/7/15　　山田花子　　12345678　22222　洗面器　　1　　　　　980

上記のように御茶碗だけ除外します。



前日以前の前注文と比較して商品コード＆商品名が異なっているもののみを
表示したいです。



それとも別テーブルにて抜き出して、計算をクエリーとしたほうがいいでしょうか。
お手数をおかけします。

補足日時：2014/10/09 10:40

この回答へのお礼

http://oshiete.goo.ne.jp/qa/8760259.html
は、本当に動かないのでしょうか
これの回答で、質問者さんのレベルを計らせてもらいます
（噛み砕いた説明が必要なのかを判断する為）


↑
ありがとうございます。こちらは以下で問題なく動いています。

SELECT * FROM ★★ AS Q1 WHERE 購入回数=
(SELECT Max(購入回数) FROM ★★ WHERE 会員番号=Q1.会員番号);

お礼日時：2014/10/09 11:11

No.2 回答者： 30246kiku 回答日時： 2014/10/09 13:33

＃１です

補足ありがとうございます。
同一受付日のレコードも存在する・・・理解できました

追加で補足をお願いしていいですか

１）
私の聞き方も悪く、
以下サンプルのように、6/1 除外するものだけの場合、
7/15 での「切り替え」の値を求める際の起点日はいつになるのでしょうか？
6/1 ? 5/1 ?

受付日　　氏名　　　　電話番号　　商品コード　商品名　数量　価格
2014/4/1　　山田花子　　12345678　55555　魔法瓶　　1　　　　2980
2014/4/2　　大島太陽　　34567890　55555　魔法瓶　　1　　　　2980
2014/5/1　　山田花子　　12345678　33333　御茶碗　　1　　　　1280
2014/5/14　　大島太陽　　34567890　55555　魔法瓶　　2　　　　5960
2014/6/1　　山田花子　　12345678　33333　御茶碗　　2　　　　2560
2014/7/15　　山田花子　　12345678　22222　洗面器　　1　　　　　980
2014/8/19　　佐藤一郎　　56789870　11111　お箸　　1　　　　　580

２）
サンプルデータを変更して、
7/15 の商品コードを「御茶碗」に変更しました。
この時、7/15 は表示するんでしょうか？

受付日　　氏名　　　　電話番号　　商品コード　商品名　数量　価格
2014/4/1　　山田花子　　12345678　55555　魔法瓶　　1　　　　2980
2014/4/2　　大島太陽　　34567890　55555　魔法瓶　　1　　　　2980
2014/5/1　　山田花子　　12345678　33333　御茶碗　　1　　　　1280
2014/5/14　　大島太陽　　34567890　55555　魔法瓶　　2　　　　5960
2014/6/1　　山田花子　　12345678　33333　御茶碗　　2　　　　2560
2014/6/1　　山田花子　　12345678　55555　魔法瓶　　1　　　　2980
2014/7/15　　山田花子　　12345678　33333　御茶碗　　1　　　　1280
2014/8/19　　佐藤一郎　　56789870　11111　お箸　　1　　　　　580

３）
> 商品コード＆商品名が異なっているもののみを
という表現を使われていますが、
同じ商品名で、商品コードが異なる事は良くあると思いますが、逆に
同じ商品コードで、商品名が異なる事はありますか？

４）
山田花子だけを見る分には意味のありそうな「切り替え」かもしれませんが、
１レコードだけの、佐藤一郎。
２レコードあるけど、商品コード＆商品名が同じ、大島太陽。
表示上では両者とも１レコードになってしまいますが、何を求めたいのでしょう？

１）～３）は、SQL を組み立てる際に重要です

この回答への補足

そこまで丁寧にみていただけるとは・・ありがとうございます。
感銘します。

スイッチイング（商品変動）を追うのに苦心しております。


ご質問の件、以下のとおりでございます。
（できるものでしょうか）



↓↓↓↓↓↓↓↓↓↓↓


＃１です

補足ありがとうございます。
同一受付日のレコードも存在する・・・理解できました

追加で補足をお願いしていいですか

１）
私の聞き方も悪く、
以下サンプルのように、6/1 除外するものだけの場合、
7/15 での「切り替え」の値を求める際の起点日はいつになるのでしょうか？
6/1 ? 5/1 ?

受付日　　氏名　　　　電話番号　　商品コード　商品名　数量　価格
2014/4/1　　山田花子　　12345678　55555　魔法瓶　　1　　　　2980
2014/4/2　　大島太陽　　34567890　55555　魔法瓶　　1　　　　2980
2014/5/1　　山田花子　　12345678　33333　御茶碗　　1　　　　1280
2014/5/14　　大島太陽　　34567890　55555　魔法瓶　　2　　　　5960
2014/6/1　　山田花子　　12345678　33333　御茶碗　　2　　　　2560
2014/7/15　　山田花子　　12345678　22222　洗面器　　1　　　　　980
2014/8/19　　佐藤一郎　　56789870　11111　お箸　　1　　　　　580



----＞　起点日は直前の6/1になります。（もし難しいようであれば「切り替え」の値はすべて、その
方の最初の受付日を見る、でも構わないです---変動タイミングを抑えたい）。





２）
サンプルデータを変更して、
7/15 の商品コードを「御茶碗」に変更しました。
この時、7/15 は表示するんでしょうか？

受付日　　氏名　　　　電話番号　　商品コード　商品名　数量　価格
2014/4/1　　山田花子　　12345678　55555　魔法瓶　　1　　　　2980
2014/4/2　　大島太陽　　34567890　55555　魔法瓶　　1　　　　2980
2014/5/1　　山田花子　　12345678　33333　御茶碗　　1　　　　1280
2014/5/14　　大島太陽　　34567890　55555　魔法瓶　　2　　　　5960
2014/6/1　　山田花子　　12345678　33333　御茶碗　　2　　　　2560
2014/6/1　　山田花子　　12345678　55555　魔法瓶　　1　　　　2980
2014/7/15　　山田花子　　12345678　33333　御茶碗　　1　　　　1280
2014/8/19　　佐藤一郎　　56789870　11111　お箸　　1　　　　　580

-----＞　直近（前）の受注と比較して同じ商品コード・名じゃないものが一つでもあった際は、変動が
あったと判断し表示させたいです。




３）
> 商品コード＆商品名が異なっているもののみを
という表現を使われていますが、
同じ商品名で、商品コードが異なる事は良くあると思いますが、逆に
同じ商品コードで、商品名が異なる事はありますか？

-----＞　ほとんどないです。商品コードで賄えると思いますが、念のため、稀に
キャンペーン等において商品名を一部、変えてつける会社もいたので商品コード＆名としました。
（もし難しいようであれば「商品コード」のみでも全く構わないです-稀なケースに合わせて
できない・・では本末転倒なので）。




４）
山田花子だけを見る分には意味のありそうな「切り替え」かもしれませんが、
１レコードだけの、佐藤一郎。
２レコードあるけど、商品コード＆商品名が同じ、大島太陽。
表示上では両者とも１レコードになってしまいますが、何を求めたいのでしょう？


変動がある方だけを表示させたいので、変動がないものは反映する必要はないです。






１）～３）は、SQL を組み立てる際に重要です

補足日時：2014/10/09 14:52

No.3 ベストアンサー 回答者： 30246kiku 回答日時： 2014/10/09 19:11

＃２です

SQLの記述量は増えましたが、＃２で提示した２つのサンプルでの結果は以下の通りです。

１）の場合 7/15 の「切り替え」は、表示外 6/1 からのものになります

受付日　　切り替え　　氏名　　電話番号　商品コード　商品名　数量　価格
2014/04/01　　　　　山田花子　12345678　　55555　　魔法瓶　　1　　2,980
2014/05/01　　30　　山田花子　12345678　　33333　　御茶碗　　1　　1,280
2014/07/15　　44　　山田花子　12345678　　22222　　洗面器　　1　　980

２）の場合 6/1 の「御茶碗」は表示外

受付日　　切り替え　　氏名　　電話番号　商品コード　商品名　数量　価格
2014/04/01　　　　　山田花子　12345678　　55555　　魔法瓶　　1　　2,980
2014/05/01　　30　　山田花子　12345678　　33333　　御茶碗　　1　　1,280
2014/06/01　　31　　山田花子　12345678　　55555　　魔法瓶　　1　　2,980
2014/07/15　　44　　山田花子　12345678　　22222　　御茶碗　　1　　1,280


テーブル名を★★と仮定します
※ 変な所で折り返し表示になるかもしれませんが、メモ帳とかにコピーしてみてください。
※ 記述の仕方はいろいろあると思いますけど・・・動いたみたいだったので


SELECT T1.受付日, T1.受付日-T2.受付日 AS 切り替え,
T1.氏名, T1.電話番号, T1.商品コード, T1.商品名, T1.数量, T1.価格 FROM
(SELECT Q1.* FROM ★★ AS Q1 INNER JOIN
(SELECT 氏名, 電話番号 FROM ★★
GROUP BY 氏名, 電話番号
HAVING Min(商品コード & 商品名)<>Max(商品コード & 商品名)
) AS Q2 ON Q1.氏名=Q2.氏名 AND Q1.電話番号=Q2.電話番号
) AS T1
LEFT JOIN
(SELECT DISTINCT Q1.受付日, Q1.氏名, Q1.電話番号 FROM ★★ AS Q1 INNER JOIN
(SELECT 氏名, 電話番号 FROM ★★
GROUP BY 氏名, 電話番号
HAVING Min(商品コード & 商品名)<>Max(商品コード & 商品名)
) AS Q2 ON Q1.氏名=Q2.氏名 AND Q1.電話番号=Q2.電話番号
) AS T2
ON T1.氏名=T2.氏名 AND T1.電話番号=T2.電話番号 AND T1.受付日>T2.受付日
WHERE T2.受付日 Is Null OR (T2.受付日=
(SELECT Max(受付日) FROM ★★ WHERE 氏名=T1.氏名 AND 電話番号=T1.電話番号 AND 受付日<T1.受付日)
AND
Exists (SELECT 1 FROM ★★ WHERE 受付日=T2.受付日 AND 氏名=T1.氏名 AND 電話番号=T1.電話番号
AND 商品コード & 商品名<>T1.商品コード & T1.商品名))
ORDER BY T1.氏名, T1.電話番号, T1.受付日;


なお、佐藤一郎、大島太陽は、処理上１レコードにしかならないので、対象外とするようにしました。

また、
> またテーブルの列は追加（集計ロジックに関係しないただの列）されていくこともあり、テーブルについては列全部をクエリーでは自動反映するとしたい。
> （わざわざその都度、追加列を指定したくない）

この部分、おそらく SELECT * FROM の * の様な記述で・・・と思われますが、
* を使った場合、「受付日」と「氏名」の間に「切り替え」を挿入する方法について、
思いつかなかったので、提示した SQL 先頭２行の所

> SELECT T1.受付日, T1.受付日-T2.受付日 AS 切り替え,
> T1.氏名, T1.電話番号, T1.商品コード, T1.商品名, T1.数量, T1.価格 FROM

部分に、追加したいフィールドがあったら、T1. を付けた記述で追加してください。


※※ 上記提示 SQL には抜けがあると思います。
例えば、
・5/1 に「御茶碗」「魔法瓶」の２つが・・・
・6/1 にも「御茶碗」「魔法瓶」の２つが・・・
この場合、6/1 の「御茶碗」「魔法瓶」は表示されます。

というのは、6/1 の「御茶碗」の表示を確認する際、5/1 に「御茶碗」以外があるか・・・
「魔法瓶」があるので、表示する様に・・・
もう１つの 6/1 の「魔法瓶」の表示を確認する際、5/1 に「魔法瓶」以外があるか・・・
「御茶碗」があるので、表示する様に・・・
つまり、１日の受付が複数の個数で、その個数が一致し、内容も一致したものが連続する場合、
表示されます。
必要であれば、考えてみますが・・・・補足ください
（補足があったとしても、回答は期待はしないでください）


※ サンプル上での動きしか見てないので、また、データ量が多くなった場合？？

この回答への補足

このレアケースを除くと完璧に表示されています！！！！ありがとうございます！！

↓↓↓
＞＞
というのは、6/1 の「御茶碗」の表示を確認する際、5/1 に「御茶碗」以外があるか・・・
「魔法瓶」があるので、表示する様に・・・
もう１つの 6/1 の「魔法瓶」の表示を確認する際、5/1 に「魔法瓶」以外があるか・・・
「御茶碗」があるので、表示する様に・・・
つまり、１日の受付が複数の個数で、その個数が一致し、内容も一致したものが連続する場合、
表示されます。
必要であれば、考えてみますが



仮に、「１日の受付が複数の個数で、その個数が一致し、内容も一致したものが連続する場合」は
スイッチングをしていないので、これは連続する後ろの方は変動なしとして非表示とできるもの
なのでしょうか？？？？


むずかしいでしょうか。

補足日時：2014/10/10 16:20

この回答へのお礼

うおーーーすごい！すみません新幹線で出張しており、今拝見しました。

ここまで考慮してくれて恐縮です（しかもレアケースまで）。


今日中に検証してみます。


取り急ぎお礼です！

お礼日時：2014/10/10 15:50

No.4 回答者： 30246kiku 回答日時： 2014/10/11 11:01

＃３です

以下でどうなりますか

SELECT T1.受付日, T1.受付日-T2.受付日 AS 切り替え,
T1.氏名, T1.電話番号, T1.商品コード, T1.商品名, T1.数量, T1.価格 FROM
(SELECT Q1.*, Q3.CT FROM (★★ AS Q1 INNER JOIN
(SELECT 氏名, 電話番号 FROM ★★
GROUP BY 氏名, 電話番号
HAVING Min(商品コード & 商品名)<>Max(商品コード & 商品名)
) AS Q2 ON Q1.氏名=Q2.氏名 AND Q1.電話番号=Q2.電話番号)
INNER JOIN
(SELECT 受付日, 氏名, 電話番号, Count(*) AS CT FROM ★★
GROUP BY 受付日, 氏名, 電話番号) AS Q3
ON Q1.受付日=Q3.受付日 AND Q1.氏名=Q3.氏名 AND Q1.電話番号=Q3.電話番号
) AS T1
LEFT JOIN
(SELECT 受付日, 氏名, 電話番号, Count(*) AS CT FROM ★★
GROUP BY 受付日, 氏名, 電話番号) AS T2
ON T1.氏名=T2.氏名 AND T1.電話番号=T2.電話番号 AND T1.受付日>T2.受付日
WHERE T2.受付日 Is Null OR (T2.受付日=
(SELECT Max(受付日) FROM ★★ WHERE 氏名=T1.氏名 AND 電話番号=T1.電話番号 AND 受付日<T1.受付日)
AND
IIF(T1.CT<T2.CT,True,
T1.商品コード & T1.商品名 Not In
(SELECT 商品コード & 商品名 FROM ★★ WHERE 受付日=T2.受付日 AND 氏名=T1.氏名 AND 電話番号=T1.電話番号)
))
ORDER BY T1.氏名, T1.電話番号, T1.受付日;

この回答へのお礼

返信が遅くなり申し訳ございません。
御礼申し上げます。まだ検証していませんが、動作確認をしてみます。

ありがとうございます！

お礼日時：2014/10/12 00:05