• Status: Solved
• Priority: Medium
• Security: Public
• Views: 455

# Help calculating differences in values based on Date in Access

I have a table in access that have ID, Values and Date.  I have sorted the data based on ID and Date.  I want another field that will show the difference between value of previous  date and the current date and so on. Here is my sample data and expected Value in the Difference column
ID      Value      Date      Difference
10024      7      6/10/2013      0
10024      6      6/17/2013      1
10024      6      6/24/2013      0
10024      6      7/1/2013      0
10024      6      7/8/2013      0
10024      6      7/15/2013      0
10024      7      7/22/2013      -1
10024      6      7/29/2013      1
10024      6      8/5/2013      0
10024      6      8/12/2013      0
10024      6      8/19/2013      0
10024      6      8/26/2013      0
10005      9      6/10/2013      0
10005      9      6/17/2013      0
10005      9      6/24/2013      0
10005      9      7/1/2013      0
10005      9      7/8/2013      0
10005      9      7/15/2013      0
10005      9      7/22/2013      0
10005      9      7/29/2013      0
10005      9      8/5/2013      0
10005      9      8/12/2013      0
10005      9      8/19/2013      0
10005      9      8/26/2013      0
Sample-Data.xlsx
0
fb1990
• 4
• 3
1 Solution

Commented:
Look at sample. There is NULL value if previous Date is missing, you can use nz function if you like to have 0 in this case
DBdiff.accdb
0

Author Commented:
Hello als315,

This works, but when I changed the Table1 to reflect my actual table name in my database.  I get a enter parameter for ID and Date.

Can you please explain how to implement this to me?  The actual ID name in my database is cust_id

Thanks.
0

Commented:
There is nested query in last column. You should change also there Table1 to real name:

SELECT Table1.ID, Table1.Value, Table1.Date, (SELECT TOP 1 [T].[VALUE] FROM Table1 As T
Where (T.ID = Table1.ID AND T.Date < Table1.Date) ORDER BY [T].[Date] DESC)-[Table1].[Value] AS Datep
FROM Table1
ORDER BY Table1.ID DESC , Table1.Date;

I see fields Date and Value in your table - it was not very good idea to use reserved words as a field name.
0

Author Commented:
Worked like a charm!  Thank you so much!
0

Author Commented:
All I have to say is, Thank you!
0

Author Commented:
One more question.  How can i populate the blank values with 0 in the Datep field?

Also for some reason my queries are running very slow... Would you have an idea why?
0

Commented:
... NZ((SELECT TOP 1 [T].[VALUE] FROM Table1 As T
Where (T.ID = Table1.ID AND T.Date < Table1.Date) ORDER BY [T].[Date] DESC), [Table1].[Value])  - [Table1].[Value] .....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.