Need your help with Query in access

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
rfedorovAsked:
Who is Participating?
 
Shaun KlineLead Software EngineerCommented:
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
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.

All Courses

From novice to tech pro — start learning today.