fb1990
asked on
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
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
ASKER
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
Please with implementation.
Thanks.
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
Please with implementation.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Worked like a charm! Thank you so much!
ASKER
All I have to say is, Thank you!
ASKER
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?
Also for some reason my queries are running very slow... Would you have an idea why?
... 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] .....
Where (T.ID = Table1.ID AND T.Date < Table1.Date) ORDER BY [T].[Date] DESC), [Table1].[Value]) - [Table1].[Value] .....
DBdiff.accdb