Subtract fields in detail

[dful]

I am creating a report that lists amounts in the detail from records in a table.

ex.
Year #1 #2
2003 5000 800
2002 4000 800
2001 3000 1200

I am trying to find the difference between the previous record.
ex.
Year #1 diff #2 diff
2003 5000 -1000 800 -400
2002 4000 -1000 400 800
2001 3000 3000 1200 1200

How can this be done??

 

[PaulBricker]

You can't do this in a Report, but you can do it in a couple queries and then use the second query as the Record Source for your Report.

The sql for the queries would look like this.

SELECT Table.Year, Max(Table_1.Year) AS MaxOfYear
FROM Table, Table AS Table_1
WHERE (((Table_1.Year)<

.[Year]))
GROUP BY Table.Year
ORDER BY Table.Year DESC;

This should produce records that look like this

Year   MinOfYear
2003     2002
2002     2001

Then the SQL for the second query would look like this.

SELECT Table.Year, Table.fld1, Table_1.fld1, [Table_1].[#1]-

.[#1] AS Expr1, Table.#2, Table_1.#2, [TAble_1].[#2]-

.[#2] AS Expr2
FROM Table AS Table_1 INNER JOIN (Table INNER JOIN Query1 ON Table.Year = Query1.Year) ON Table_1.Year = Query1.MaxOfYear;

Table is the name of your table. Table_1 is also the name of your Table. When Access sees two tables of the same name in a query, it adds the _1 suffix to the Table name.
Query1 is the first query you created.

This will get you close, but you may have to do some fudging to get the data the way you want.

Paul