Help calculating differences in values based on Date in Access

Posted on 2014-02-22
Medium Priority
Last Modified: 2014-02-23
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
Question by:fb1990
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 40

Expert Comment

ID: 39880184
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

Author Comment

ID: 39880577
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.

LVL 40

Accepted Solution

als315 earned 1000 total points
ID: 39880590
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.
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.


Author Comment

ID: 39880604
Worked like a charm!  Thank you so much!

Author Closing Comment

ID: 39880606
All I have to say is, Thank you!

Author Comment

ID: 39880620
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?
LVL 40

Expert Comment

ID: 39881564
... 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] .....

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question