Calculate difference from previous record in report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want a report that looks like the following. How do I calculate the Diff
column in a report?

Date Value Diff

01/19 11 (5)
01/18 16 6
01/17 10 3
01/16 7 -
 
Create a query into this table (called Table1 in this example.)

In a fresh column in the Field row, enter something like this:
Diff: (SELECT TOP 1 Value FROM Table1 AS Dupe
WHERE Dupe.Date < Table1.Date
ORDER BY Dupe.Date DESC, Value)

The query will show the Diff column.
You can now create a report that uses this query as its Record Source.

The calculated query field is a subquery. If you want to know more about
creating subqueries, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
 
Back
Top