Solved

Need your help with Query in access

Posted on 2014-01-09
1
198 Views
Last Modified: 2014-01-11
Rec#      LookUpDate      Amount1                      Amount2
58      9/23/2013      72,599.00      33,795.00
59      10/23/2013      75,753.00      35,063.00
60      11/8/2013      75,046.00      35,063.00
61      12/9/2013      78,256.00      35,700.00
62      1/8/2014                       80,615.00      36,340.00

 twice a month, i input two amounts :Amount1      and Amount2      in the database,
how to write a query to see the difference in last and previous records, in case like this should be:
RecentDate               Diff1                  Diff1
1/8/2014                       2,359.00                 640.00
0
Comment
Question by:rfedorov
1 Comment
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 39768652
You can use a sub query in your select clause to find the prior row:

select LookupDate, Amount1, Amount2, 
   (select top 1 Amount1 from <yourtable> b where b.LookupDate < a.LookupDate Order by b.LookupDate Desc) AS PriorAmount1,
  (select top 1 Amount2 from <yourtable> b where b.LookupDate < a.LookupDate Order by b.LookupDate Desc) AS PriorAmount2
from <yourtable> a

Open in new window


For large data sets, this query may take time to return.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Correct ordering 16 44
total hours between two times grouped by type 6 29
Access MDB/PDF 21 32
Resize text 4 16
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…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

757 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

25 Experts available now in Live!

Get 1:1 Help Now