I am having some difficulty with a SQL query to show the deference between previous and current record. The query works fine if previous record is less than current record. I'm having trouble when the previous record value is the same as the current value or a zero is entered. Below is query I have now..
SELECT date, table.value, (SELECT MAX(value) FROM table AS Pure WHERE Pure.value < table.value) AS Prevalue, table.value-Prevalue AS Difference
Example when value is less than current.
Date Value Prev Diff
5/5/09 850 650 200
5/6/09 950 850 100
5/7/09 1000 950 50
Yes, it could be more than one record per day. What I ultimately want is to compare the previous record regardless of how many entries per day. I get the same problem if I do date or value. I'm very new at sql and access so I'm not really sure how to accomplish this task.
SELECT Table.Date, Table.Value, DLookUp("[Value]","Table","ID=" & [ID]-1) AS Prev, [Value]-[Prev] AS Diff, (SELECT Sum(Value- DLookUp("[Value]","Table","ID=" & [ID]-1)) FROM [Table] AS temp WHERE temp.[ID] <= [Table].[ID]) AS SumDiff