- 締切済み
ACCESSを活用して累計(経過)を表現したい
ACCESSテーブルに以下情報が格納されています。 注文日 会員番号 商品名 価格 2014/03/23 123456 サンプル 500円 2014/04/23 123456 サンプル 500円 2014/05/23 123456 サンプル 500円 2014/03/21 456789 容器 1000円 2014/04/21 456789 容器 1000円 上記をクエリーを利用して、以下の抽出をする方法は ないでしょうか。 ↓ (1) 会員番号 購入回数 価格 累積価格 123456 1 500 500 123456 2 500 1000 123456 3 500 1500 456789 1 1000 1000 456789 2 1000 2000 (2) 会員番号 購入回数 LTV 123456 1 0 123456 2 30 123456 3 60 456789 1 30 456789 2 60 ※LTV:その回の注文日 - 前の注文日 ※必要であれば先頭にAUTOナンバーをつけることも可能 ※ACCESS2010を利用中 お手数ですが御教授いただけるとたすかります。。
- jordan232011
- お礼率35% (53/148)
- オフィス系ソフト
- 回答数4
- ありがとう数2
- みんなの回答 (4)
- 専門家の回答
みんなの回答
- 山田 太郎(@f_a_007)
- ベストアンサー率20% (955/4574)
補足:DBLookup() 先のSQL文では、SELECT文中にカウントと集計用のSELECT文を埋め込んでいます。もちろん、それぞれに特化したAccessの関数を用いても事足ります。しかし、(1)SQL文を明示的に書きたい、(2)実行速度を担保したい。などなどの理由でDBLookup()は有用です。もう一つの答は、SELECT MAX(注文日) FROM ・・・、SELECT MIN(注文日) FROM ・・・という二つのSELECT文を組み込むことになるのかも知れません。 Public Function DBLookup(ByVal strQuerySQL As String, _ Optional ByVal ReturnValue = Null) As Variant On Error GoTo Err_DBLookup Dim DataValue Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset With rst .Open strQuerySQL, _ CurrentProject.Connection, _ adOpenStatic, _ adLockReadOnly If Not .BOF Then .MoveFirst DataValue = .Fields(0) End If End With Exit_DBLookup: On Error Resume Next rst.Close Set rst = Nothing DBLookup = IIf(Len(DataValue & ""), DataValue, ReturnValue) Exit Function Err_DBLookup: MsgBox "SELECT 文の実行時にエラーが発生しました。(DBLookup)" & Chr$(13) & Chr$(13) & _ "・Err.Description=" & Err.Description & Chr$(13) & _ "・SQL Text=" & strQuerySQL, _ vbExclamation, " 関数エラーメッセージ" Resume Exit_DBLookup End Function
- 山田 太郎(@f_a_007)
- ベストアンサー率20% (955/4574)
補足: テスト結果で多少は修正を! ORDER BY 会員別注文履歴.会員番号, 会員別注文履歴.価格, 会員別注文履歴.ID, こっちが意図した結果に!
- 山田 太郎(@f_a_007)
- ベストアンサー率20% (955/4574)
添付図のクエリのSQLビューです。 SELECT 会員番号, ID, 価格, DBLookup( "SELECT COUNT(*) FROM 会員別注文履歴 WHERE 会員番号=" & 会員番号 & " AND ID<=" & 会員別注文履歴.ID) AS 購入回数, DBLookup( "SELECT Sum(価格) FROM 会員別注文履歴 WHERE 会員番号=" & 会員番号 & " AND ID<=" & 会員別注文履歴.ID & " AND 価格=" & 会員別注文履歴.価格) AS 累積価格 FROM 会員別注文履歴 GROUP BY 会員別注文履歴.会員番号, 会員別注文履歴.ID, 会員別注文履歴.価格 ORDER BY 会員別注文履歴.会員番号, 会員別注文履歴.ID, 会員別注文履歴.価格; ここではDBLookup()を使っていますが DLookup() と同じです。 ※シリアル番号=一連の一意で等差な番号。 ここでは、列[ID]を意味します。
- 山田 太郎(@f_a_007)
- ベストアンサー率20% (955/4574)
(1) 1、<会員番号+シリアル番号+価格>昇順でデータを抜き出す。 2、抜き出した各行で購入回数、価格、累積価格を求める。 (2) 1、<会員番号+シリアル番号>昇順でデータを抜き出す。 2、抜き出した各行で購入回数、LTVを求める。 Access のバージョンに関係なく、こんな感じかと思います。
お礼
なるほど、更新クエリ等も活用すればできそうですね。ありがとうございます!
補足
すみません、基本的なことで非常に申し訳ないのですが、 ・シリアル番号とは? ・このやり方でLTVはでるのでしょうか??イメージを知りたく
関連するQ&A
- ACCESSデータは5000以上ある(抽出の件)
データは5000件以上あるのですが、以下で対応は難しいのではないでしょうか。 ソートでできますでしょうか?↓ http://okwave.jp/qa/q8755953.html ●質問した内容● ACCESSを活用して累計(経過)を表現したい 質問者:jordan232011 投稿日時:2014/09/15 21:38 困ってます ACCESSテーブルに以下情報が格納されています。 注文日 会員番号 商品名 価格 2014/03/23 123456 サンプル 500円 2014/04/23 123456 サンプル 500円 2014/05/23 123456 サンプル 500円 2014/03/21 456789 容器 1000円 2014/04/21 456789 容器 1000円 上記をクエリーを利用して、以下の抽出をする方法は ないでしょうか。 ↓ (1) 会員番号 購入回数 価格 累積価格 123456 1 500 500 123456 2 500 1000 123456 3 500 1500 456789 1 1000 1000 456789 2 1000 2000 (2) 会員番号 購入回数 LTV 123456 1 0 123456 2 30 123456 3 60 456789 1 30 456789 2 60 ※LTV:その回の注文日 - 前の注文日 ※必要であれば先頭にAUTOナンバーをつけることも可能 ※ACCESS2010を利用中 お手数ですが御教授いただけるとたすかります。。
- ベストアンサー
- オフィス系ソフト
- ●最終系:ACCESSクエリーの件!懇願mm
ACCESSクエリーで教えていただきたいことございます! 以下に関連しております。大変恐縮です。 ↓ http://okwave.jp/qa/q8756016.html ●したいこと● 累積回数、LTVを導きたいです(自動算出)。 ●何から?● 以下データ群(テーブル名:table1)があります(会員番号はユニークをあらわします)。 注文日 会員番号 商品名 価格 2014/04/11 123456 パンツ 1000 ←一番古い注文日の行(4/11開始)が先頭 2014/04/20 123456 靴下 3000 2014/05/23 456789 半袖 2000 2014/05/23 456789 靴下 3000 2014/05/29 456789 パンツ 1000 2014/05/30 987654 靴下 3000 2014/06/09 987654 寝巻き 5000 2014/06/15 987654 下駄 4000 ●SQLクエリーで導きだしたいこと● 上記データ群を、SQL(クエリー)を使用して、 以下のように表現できないでしょうか。 注文日 会員番号 購入回数 LTV ※商品名も表示できればそのままつけたい 2014/04/11 123456 1 0 2014/04/20 123456 2 9 2014/05/23 456789 1 0 2014/05/23 456789 1 0 2014/05/29 456789 2 6 2014/05/30 987654 1 0 2014/06/09 987654 2 10 2014/06/15 987654 3 16 ※同じ注文日、同じ会員番号であれば、商品を複数購入しようが(複数行であっても)それは同日扱いにより購入回数は同じと数とする ※LTV:その回の注文日 - 最初(1回目)の注文日(最初の購入から見て何日経過か) ※ユニーク(会員番号)の方が、一度にいくつ購入したかは問わず、 何日おき(すべて初回を起点にした経過日とする)に来店したか、頻度と合わせて表現したい ※積み上がる累積金額の表示はなくてかまわないので、上記(購入回数、LTV)2つを表示できないでしょうか。 お知恵をいただければ幸いですmm LTV算出は以下のイメージが近いです。 ↓↓↓↓↓↓↓↓↓↓↓ http://okwave.jp/qa/q8756016.html なお、LTV を求める記述の Max(Q2.注文日) を Min(Q2.注文日) とすると 表示結果は、 会員番号 購入回数 LTV 123456 1 0 123456 2 31 123456 3 61 456789 1 0 456789 2 31
- ベストアンサー
- その他MS Office製品
- ACCESS SQLクエリーの活用について
以下データ群(テーブル名:table1)があります(会員番号はユニークをあらわします)。 注文日 会員番号 商品名 価格 2014/04/11 123456 パンツ 1000 ←一番古い注文日の行(4/11開始)が先頭 2014/04/20 123456 靴下 3000 2014/05/23 456789 半袖 2000 2014/05/23 456789 靴下 3000 2014/05/29 456789 パンツ 1000 2014/05/30 987654 靴下 3000 2014/06/09 987654 寝巻き 5000 2014/06/15 987654 下駄 4000 そして、 SELECT Q4.注文日, Q4.会員番号, Q4.商品名, Q3.購入回数, Q3.LTV FROM (SELECT Q1.注文日, Q1.会員番号, Count(Q2.会員番号)+1 AS 購入回数, DateDiff('d', Nz(Min(Q2.注文日),Q1.注文日), Q1.注文日) AS LTV FROM (SELECT 会員番号, 注文日 FROM ★★ GROUP BY 会員番号, 注文日) AS Q1 LEFT JOIN (SELECT 会員番号, 注文日 FROM ★★ GROUP BY 会員番号, 注文日) AS Q2 ON Q1.会員番号=Q2.会員番号 AND Q1.注文日>Q2.注文日 GROUP BY Q1.会員番号, Q1.注文日) AS Q3 LEFT JOIN ★★ AS Q4 ON Q3.会員番号=Q4.会員番号 AND Q3.注文日=Q4.注文日 ORDER BY Q4.会員番号, Q4.注文日 ; 達人からアドバイスいただいていた 上記SQLクエリーからの表示は 注文日 会員番号 商品名 購入回数 LTV 2014/04/11 123456 パンツ 1 0 2014/04/20 123456 靴下 2 9 2014/05/23 456789 半袖 1 0 2014/05/23 456789 靴下 1 0 2014/05/29 456789 パンツ 2 6 2014/05/30 987654 靴下 1 0 2014/06/09 987654 寝巻き 2 10 2014/06/15 987654 下駄 3 16 と出ます(GOOD!!!)。 上記に付随して、 このクエリーが参照しているテーブルの他の列(例:氏名、都道府県)もある(集計対象ではない)場合でそれも追加で(列名:氏名、都道府県を)表現するためには、 SELECT Q4.注文日, Q4.会員番号, Q4.商品名, Q3.購入回数, Q3.LTV の構文箇所に2つの列名も手打ちで追加する必要がありますでしょうか。 (テーブル列名すべてを表示する・・といった個別に列名を指定しないで表示する方法はあるのでしょうか<列名が多いときに手間が若干かかる)
- ベストアンサー
- その他MS Office製品
- 再 ACCESSのソートについて(必要なものだけ
テーブルにあるデータを以下のようにクエリーでソートしたく。 会員番号をユニークIDとする ○テーブルにあるデータ 注文日 会員番号 購入額 購入回数 2014/03/05 12345678 3675 10 2014/03/05 12345678 3675 9 2014/03/05 98765241 3675 3 上記について会員番号が同一であれば、購入回数が 最大のものだけを行表示させる(以下)ことは 可能でしょうか。 注文日 会員番号 購入額 購入回数 2014/03/05 12345678 3675 10 2014/03/05 98765241 3675 3 会員番号が重複している行は購入回数が最大の数の行だけ残したい。 (重複ありなし混在で1万ほどレコードがある) お手数をおかけします!
- ベストアンサー
- オフィス系ソフト
- 歯抜けとなっている特定の会員だけを特定したい
注文日 会員番号 商品名 価格 注文番号 購入回数 2014/3/2 123345678 お好み焼き 550 201401123 1 上記行毎の注文データがxlsで1万件(行)あります。 (「購入回数」は日別で注文された累積件数をあらわします。1=初回 2=2回目 最大5迄ふられている) <問題> 同じ会員番号のデータで、購入回数[1]がないにもかかわらず、購入回数2以降~から存在する不要な データが混ざっています。 この購入回数1がないのに、同じ会員番号で購入回数が2~5いずれかをふられている行(会員)だけを ピックアップする方法はないでしょうか。xls上でもアクセスにインポートしてからの 処理でもどちらでもかまわないのですが。 いわゆるこの不要データを削除(クリーニング)したいのです。 お知恵をいただけるとありがたいですmm
- ベストアンサー
- Excel(エクセル)
- ACCESSを活用して商品変動を捉えたい(2)
以下受注情報(テーブル)があります。 受付日 氏名 会員番号 商品コード 商品名 数量 価格 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 上記について、会員番号が同じお客様の場合で、 商品コード、商品名が前のリストから変わったリストのみ クエリー(SQL?)で、以下のように抽出することは難しいでしょうか。 (変わったという判断は直前を見る) 受付日 切り替え 氏名 会員番号 商品コード 商品名 数量 価格 2014/4/1 山田花子 12345678 55555 魔法瓶 1 2980 2014/5/1 30 山田花子 12345678 33333 御茶碗 1 1280 2014/7/15 75 山田花子 12345678 22222 洗面器 1 980 切り替え:前に注文した日付からの経過(日)を反映したい。 またテーブルの列は追加(集計ロジックに関係しないただの列)されていくこともあり、テーブルについては列全部をクエリーでは自動反映するとしたい。 (わざわざその都度、追加列を指定したくない) お手数ですがご教授いただければ幸いです(願)。
- ベストアンサー
- オフィス系ソフト
- ACCESSを活用して商品変動を捉えたい(2再再)
続きです。 http://okwave.jp/qa/q8781159.html SELECT DBLookup("SELECT 受注日 FROM 受注履歴 WHERE 会員番号='" & [会員番号] & "' AND 受注日 < #" & [受注日] & "# ORDER BY 受注日 DESC") AS 前回受注日, DateDiff("d",前回受注日,受注日) AS 切り替え, * FROM 受注履歴; 上記に対して、 すみません、以下はどこに組み込まれるのでしょうか。 >・SELECT文全体に、会員番号昇順で並べるORDER BY 節が欠落しています。 >・WHERE節を追加されると目的は達成されます。 注文1回のお客は除きます。。
- ベストアンサー
- オフィス系ソフト
- ACCESSを活用して商品変動を捉えたい(6再再)
クエリー14 http://okwave.jp/qa/q8781488.html を参考にSELECT構文を作っていますが、、、 上記URLの画面のようには出ず、以下のようになります。 ※商品名、商品コードが変わらない「大島太陽」も抽出される(数量、価格が変化しているだけ) ※ 前回受注日 切り替え 受注日 氏名 会員番号 商品コード 商品名 数量 価格 2014/04/01 山田花子 12345678 55555 魔法瓶 1 2980 2014/04/01 30 2014/05/01 山田花子 12345678 33333 御茶碗 1 1280 2014/05/01 75 2014/07/15 山田花子 12345678 22222 洗面器 1 980 2014/04/02 大島太陽 34567890 55555 魔法瓶 1 2980 2014/04/02 42 2014/05/14 大島太陽 34567890 55555 魔法瓶 2 5960 クエリー14のイメージで間違えないでしょうか。 SELECT DBLookup("SELECT 受注日 FROM 受注履歴 WHERE 会員番号=" & [会員番号] & " AND 受注日 < #" & [受注日] & "# ORDER BY 受注日 DESC") AS 前回受注日, DateDiff("d",前回受注日,受注日) AS 切り替え, * FROM 受注履歴 WHERE (((DBLookup("SELECT COUNT(受注日) FROM 受注履歴 WHERE 会員番号=" & [会員番号]))>1)) ORDER BY 受注履歴.会員番号, 受注履歴.受注日;
- ベストアンサー
- その他MS Office製品
- ACCESSかエクセルで抽出したいmm(再掲載)
以下出荷データ(xls.)があるのですが、同じ「会員番号」で見て、購入回数別で 「商品名・区分の両方ないしはいずれか」が1回目と変わっているデータ(会員番号)は 1回目含めて除外、ないしは目印をつけたいの ですが、EXCEL、ないしはACCESSで行う方法ないでしょうか。 ※浮気をしないで一貫しておなじ「商品名・区分」を貫いているお客(恋人)だけを 引っ張り出したいmm 購入回数1しかない顧客は浮気対象ではない(除外しない) 以下は出荷履歴の一部を切り取ったものです(これを活用したい) EXCELでできてしまうのか?ACCESS SQLがいいのか? 注文日 会員番号 商品名 区分 購入回数 2014/03/01 12345678 セレクト 通常購入 1 2014/04/01 98765432 通常商品 定期購入 1 2014/04/10 33333333 通常商品 通常購入 1 2014/05/20 77777777 セレクト 通常購入 1 2014/06/20 77777777 セレクト 通常購入 2 2014/07/20 77777777 セレクト 通常購入 3 2014/08/20 77777777 シリーズ 通常購入 4 2014/09/20 77777777 セレクト 通常購入 5 方法悩んでおりますmmmm 商品名・区分はそれぞれ商品名が上記3種類、区分は2種類しかないので、 これを数字に変換してもかまわない(整理ができればいいので)。 さすがに難しいですよねmmmm
- ベストアンサー
- Excel(エクセル)
- ACCESSを活用して商品変動を捉えたい(3再再)
こちらの件ですが、 http://okwave.jp/qa/q8781488.html 最終的に以下で行こうと思っています。 テーブル名:スイッチング SQL構文 SELECT; DBLookup("SELECT 受注日 FROM スイッチング WHERE 会員番号=" & [会員番号] & " AND 受注日 < #" & [受注日] & "# ORDER BY 受注日 DESC") AS 前回受注日, DateDiff("d",前回受注日,受注日) AS 切り替え, * FROM スイッチング WHERE (((DBLookup("SELECT COUNT(受注日) FROM スイッチング WHERE 会員番号=" & [会員番号]))>1)) ORDER BY スイッチング.会員番号, スイッチング.受注日; しかしながら、クエリ実行を押すと、以下メッセージが流れ動作しません。 何が不足しているのでしょうか。 ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓ !SELECT ステートメントが間違っている予約語や引数を含んでいるか、 区切り記号が正しくありません。
- 締切済み
- その他MS Office製品
お礼
丁寧な回答に深く御礼申し上げます。検討、試行してみますVV