Calculating records in same table Cost-cost for previous year

[marrett]

I am creating a dtabase and need to know how to calculate recrds. Table Fields: CoID, Year, Cost.

I need subtract the cost of one year form the previous year.

Explaintion: [Cost]of [year] - Cost of previous year.


I hope I am clear enough.

Thnaks for your help.

Maria

 

[TexasTJ]

One way would be to set up a couple of queries. First would contain the detail - the second would be grouped by CoID.

First query would have columns something along line of:

1) CoID
2) 1999

Year=1999)*Cost*-1
3) 1998

Year=1998)*Cost*-1
4) 1997

Year=1997)*Cost*-1
5) 99IncDec: [1999]-[1998]
6) 98IncDec: [1998]-[1997]

This first query could produce multiple records for each company (assuming a company has been around more than one year).

The second query would use the first query for its base. Add all columns from first query and turn on grouping. Set the Total row for the company column to Group By and the remaining columns to Sum.

Good luck!