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ナンバーをつけることも可能

※ACCESS２０１０を利用中

お手数ですが御教授いただけるとたすかります。。

回答No.4

山田 太郎（@f_a_007）

補足：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

回答No.3

山田 太郎（@f_a_007）

補足: テスト結果で多少は修正を！

ORDER BY　会員別注文履歴.会員番号,
　　　　　会員別注文履歴.価格,
　　　　　会員別注文履歴.ID,

こっちが意図した結果に！

お礼

丁寧な回答に深く御礼申し上げます。検討、試行してみますVV

回答No.2

山田 太郎（@f_a_007）

添付図のクエリの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]を意味します。

回答No.1

山田 太郎（@f_a_007）

（１）

１、＜会員番号＋シリアル番号＋価格＞昇順でデータを抜き出す。
２、抜き出した各行で購入回数、価格、累積価格を求める。

（２）

１、＜会員番号＋シリアル番号＞昇順でデータを抜き出す。
２、抜き出した各行で購入回数、LTVを求める。

Access のバージョンに関係なく、こんな感じかと思います。

お礼

なるほど、更新クエリ等も活用すればできそうですね。ありがとうございます！

補足

すみません、基本的なことで非常に申し訳ないのですが、

・シリアル番号とは？

・このやり方でLTVはでるのでしょうか？？イメージを知りたく