Solved

Help calculating differences in values based on Date in Access

Posted on 2014-02-22
7
422 Views
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
Sample-Data.xlsx
0
Comment
Question by:fb1990
  • 4
  • 3
7 Comments
 
LVL 39

Expert Comment

by:als315
Comment Utility
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
 
LVL 1

Author Comment

by:fb1990
Comment Utility
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.
0
 
LVL 39

Accepted Solution

by:
als315 earned 250 total points
Comment Utility
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 1

Author Comment

by:fb1990
Comment Utility
Worked like a charm!  Thank you so much!
0
 
LVL 1

Author Closing Comment

by:fb1990
Comment Utility
All I have to say is, Thank you!
0
 
LVL 1

Author Comment

by:fb1990
Comment Utility
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
 
LVL 39

Expert Comment

by:als315
Comment Utility
... 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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now