Comparing each year with the previous year
(even when some of the data is missing)

I have the following table, which tracks the value of an investment:

The field "curValue" indicates the value of the investment at the end of the year.

I'd like to find out by what percentage the value has increased each year.

That would be really easy, if tblInvestment looked like this:

If I modified tblInvestment in this way, I would be introducing a redundancy - because the starting value for each year is exactly the same as the ending value for the previous year.

But there *is* a way of getting the information I want without modifying the table structure at all - and with only one SELECT.

SELECT t2.intYear, t2.curValue,
    t2.curValue-t1.curValue AS Increase,
    (t2.curValue / t1.curValue - 1)*100 AS PercentageIncrease
FROM tblInvestment AS t1 INNER JOIN tblInvestment AS t2
    ON t2.intYear=t1.intYear+1

This query will produce the following output:

This works well, if the set of data is complete.  But what if the data for the year 2000 is missing.

Testing my query after deleting the year 2000 from tblInvestment, I get the following output:

Even with one year of data missing, there ought to be a way of determining the average annual increase between 1999 and 2001

And there is. Instead of looking back exactly one year, I can look back to the most recent year for which data is available, and divide it by the number of years spanned.

SELECT t2.intYear, t2.curValue,
    (t2.curValue-t1.curValue)/(t2.intYear-t1.intYear) AS 
AvgAnnualIncrease,
    (t2.curValue / t1.curValue - 1)/(t2.intYear-t1.intYear)*100 
AS AvgPercentageIncrease
FROM tblInvestment AS t1, tblInvestment AS t2
WHERE t1.intYear=(SELECT MAX(intYear) FROM tblInvestment AS t3 
    WHERE t3.intYear<t2.intYear)

The output of this query is:

Adelle.