Access 1レコード前の情報を取得

質問

Access2003でSQLクエリを用い、カレンダーリスト（テーブル名：T_カレンダー）の作成をしております。
カレンダーには、年月日、売上金、前日売上金、などの項目があります。

Tカレンダテーブルの内容
年月日
売上金

■目的
「前日売上金」 に、
１レコード前、もしくは前日以前のレコード（※）の
「売上金」 のデータを格納したい。

※前日以前のレコードとしたいのは
　カレンダーですので、年月日も営業日などの関係で、
　土日のデータなどは0円になることがあります。
（ちなみにデータ値がない場合は0を既定値でセット）

その為、前日の売上金が0円だった場合、
最後の営業日の売上金データがあったもの（0円以外のもの）を持って来たいと考えています（※２）。
基本、+（プラス）値となりますが、-（マイナス）値も抽出したいと考えています。

■ためしに
SELECT t1.年月日, t1.売上金, t2.年月日, t2.売上金 AS 前日売上金
FROM Tカレンダ AS t1
LEFT JOIN Tカレンダ AS t2
ON t2.年月日 = DateAdd("d",-1, t1.年月日)
ORDER BY t1.年月日;

こんなSQLを組んでみました。1日前のデータは抽出できたのですが
※２の内容がどう組んでよいか、わかりません。

どうか、ご教授願います。
宜しくお願いいたします。

回答No.2

piroin654 · ベストアンサー

あっさりと以下の方法ではどうですか。

(1)
標準モジュールに以下のコードを貼り付け保存します。
DAOを使っているので参照設定で、
Microsoft DAO xx Object Libraryにチェックを入れてください。
AADoがチェック済みならばADOより上に設定しておいてください。

Function funcDate(ByVal ddd As Date) As Date
Dim db As Database
Dim rs As Recordset
Dim i As Long
Dim Maxnum As Date
Dim Minnum As Date
Dim num As Long

Set db = CurrentDb
Set rs = db.OpenRecordset("Tカレンダ")
Maxnum = DMax("年月日", "Tカレンダ")
Minnum = DMin("年月日", "Tカレンダ")
num = DateDiff("d", Minnum, Maxnum)

For i = 1 To num - 1
If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
If rs!年月日 = ddd - i Then
If rs!売上金 > 0 Then
funcDate = rs!年月日
Exit For
End If
End If
rs.MoveNext
Loop
End If
Next i

rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
End Function

(2)
以下のSQLを新しいクエリのSQLビューに貼り付け
保存します。

SELECT t1.年月日, t1.売上金, funcDate(t1.年月日) AS 前営業日, t2.売上金 AS 前営業日売上金
FROM Tカレンダ AS t1 LEFT JOIN Tカレンダ AS t2 ON t2.年月日=funcDate(t1.年月日)
ORDER BY t1.年月日;

一番ふるい日付の前日はレコードがないので、
0:00:00になり、前営業日売上金はNullになります。
以下のようにすれば体裁整いますが、
あまり意味がないかもしれません。

SELECT t1.年月日, t1.売上金, IIf(IsNull(前営業日売上金),Null,funcDate(t1.年月日)) AS 前営業日, t2.売上金 AS 前営業日売上金
FROM Tカレンダ AS t1 LEFT JOIN Tカレンダ AS t2 ON t2.年月日=funcDate(t1.年月日)
ORDER BY t1.年月日;

お礼

助かりました。ありがとうございます。

補足

丁寧なご回答ありがとうございます。
早速、試させていただいてみたところ、
望みどおりの結果が算出されました。

あとは、この他に別の計算処理も組み込まなければいけないのですが、
多少パフォーマンスが心配ですが、後は自力で頑張ってみます。

回答No.1

Siegrune

SELECT t1.年月日, t1.売上金, t2.年月日, t2.売上金 AS 前日売上金
FROM Tカレンダ AS t1
LEFT JOIN Tカレンダ AS t2
ON t2.年月日
= (SELECT MAX(t3.年月日) from Tカレンダ AS t2 where t3.年月日 < t1.年月日 and t3.売上金 > 0)
ORDER BY t1.年月日;

という方法を使います。
（accessでは未確認。）

補足

ありがとうございます。回答感謝しております。

クエリ式't2.年月日
=(SELECT(SELECT MAX(t3.年月日) from Tカレンダ AS t2 where t3.年月日 < t1.年月日'の構文へラー

となってしまいました。
因みにON区の部分のSELECT文内の、Tカレンダ AS t2となっていた箇所は
t2→t3
という事でよいでしょうか？

=(SELECT(SELECT MAX(t3.年月日) from Tカレンダ AS t3 where t3.年月日 < t1.年月日'

取得条件は判ってきたのですが、
構文エラーから脱出できません（汗
もし、他の構文の書き方などご存知でしたら
ご教授いただけますでしょうか？