Propagation of values to subsequent years

[taps54]

I need help writing the code to select the most current value for a given
record
based on dates and propagate that value for the subsequent years to current
year if there is no data . The record has an associated cost for a given year
say 2000.
For years 2001 to 2006 there is no cost. I want to use the cost in 2000 for
the most current cost even though my date criteria specifies say 2002. Can
this be done within the select query?

Type 2000 2001 2002 2003 2004 2005 2006
test1 45 48 50 58 62 70 72
test2 25 -- --
test3 15 18 --

I need the cost from the last entered value to be placed in each subsequent
year where there is no value.

Any help would be greatly appreciated

 

[Ofer Cohen]

You can use something like

SELECT TableName.Type, TableName.[2000], Nz([2001],[2000]) AS New2001,
Nz([2002],[New2001]) AS New2002, Nz([2003],[New2002]) AS New2003,
Nz([2004],[New2003]) AS New2004, Nz([2005],[New2004]) AS New2005,
Nz([2006],[New2005]) AS New2006
FROM TableName