Getting a variable end of year date and value from MS Access table using SQL -

i have data is daily (day on day) closing figures tracked supply , in 1 ms access table has 2 columns - dates (the date), pxlast(the day's closing figure)). have daily data jan 1991 aug 2013 , wanted percentage change of pxlast @ every year end compared last year year end follows:

year | percentage change of pxlast(year on year)

1991 | 15.2% 1992 | 9.2%

the year end date varies (not 31st ) , going getting last pxlast value by:

1.get max date in dec every year: results in myyear, mymonth, myday

2.combine using dateserial(myyear, mymonth, myday)

3.join resulting query table , inner join on date column

4.get pxlast value

   select enddates.enddates, nse20.px_last lookpoint     nse20 inner join      (select dateserial([myyear],[mymonth],[myday])      enddates (select 12 mymonth, myday, myyear      (select max(day([dates])) myday, year([dates]) myyear      nse20 (((month([dates]))=12))       group year([dates])) endyearvalues)      endvaluedates)      enddates on nse20.dates = enddates.enddates; 

could assist me corresponding value using query previous year end

eg 29 dec 2006, should show current value , show value 31 dec 2005

in same row ie

year | current year end| previous year end

2005 | 3449.00 | 4611.19

2006 | 9.2% |3449.00

any appreciated.

any suggestions better way of doing very welcome....

let's assume have test data in table named [nse20] looks this

dates       pxlast ----------  ------ 2010-07-01     131 2010-12-31     130 2011-11-12     123 2011-12-30     125 2012-01-03     127 2012-12-31     129 

i'd start creating saved query in access named [nse20_year_ends] identifies year-end dates (calendar) year:

select year(dates) calendaryear, max(dates) yearenddate nse20 group year(dates) 

that produce

calendaryear  yearenddate ------------  -----------         2010  2010-12-31          2011  2011-12-30          2012  2012-12-31  

then i'd create saved query named [nse20_year_end_balances] extract closing balances each year:

select nse20_year_ends.calendaryear, nse20.pxlast      nse20     inner join     nse20_year_ends         on nse20.dates = nse20_year_ends.yearenddate 

that give us

calendaryear  pxlast ------------  ------         2010     130         2011     125         2012     129 

now can self-join on query calculate percentage change

select      y1.calendaryear,      (y1.pxlast - y0.pxlast) / y0.pxlast * 100 pctchange     nse20_year_end_balances y1     inner join     nse20_year_end_balances y0         on y0.calendaryear = y1.calendaryear - 1 

resulting in

calendaryear  pctchange         ------------  -----------------         2011  -3.84615384615385         2012                3.2