プロが教えるわが家の防犯対策術!

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を利用中


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

A 回答 (4件)

補足: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
    • good
    • 0

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



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

こっちが意図した結果に!
    • good
    • 0
この回答へのお礼

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

お礼日時:2014/09/16 01:02

添付図のクエリの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]を意味します。
「ACCESSを活用して累計(経過)を表現」の回答画像2
    • good
    • 0

(1)



1、<会員番号+シリアル番号+価格>昇順でデータを抜き出す。
2、抜き出した各行で購入回数、価格、累積価格を求める。

(2)

1、<会員番号+シリアル番号>昇順でデータを抜き出す。
2、抜き出した各行で購入回数、LTVを求める。

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

この回答への補足

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

・シリアル番号とは?

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

補足日時:2014/09/15 22:00
    • good
    • 0
この回答へのお礼

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

お礼日時:2014/09/15 21:58

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!