Reference Next Record

[jaybird19]

Hi There,

I have a report that shows totals for each year:

Year Sales
2009 $100
2008 $90
2007 $50

I need to add in a percentage to show the increase or decrease from one year to the next:

Year Sales %Change
2009 $100 111%
2008 $90 180%
2007 $50 ---

How do I reference the sales in the next record to calculate the percentage?

Thanks,
Jayme

 

[lameid]

You can...

use a "D" function like DSUM / Dlookup

Do a self join in your query to return the prior year's sales as a value and use it for the calculation.

Use VBA that uses global variables to track when the year changes and maintains the last value and use a funtion to return the global variable.

I would lean to the query option because the "D" functions are relatively slow and the coding solution is the most work but probably the most efficient.

 

[PWise]

for starters
Select SalesByyear.SaleYear,SalesByyear.Sales ,SalesPriorYear.Sales

from (
Select year(Dateofsale) SaleYear,sum(SaleAmount)Sales
From SalesTable
Group By year(Dateofsale))SalesByyear
Left join (Select year(Dateofsale) SaleYear,sum(SaleAmount)Sales
From SalesTable
Group By year(Dateofsale)))SalesPriorYear
on SalesPriorYear.SaleYear=SalesByyear.SaleYear+1