I have a table:
country election party vote
usa 1/1/2000 dem 48
usa 1/1/2004 dem 49
usa 1/1/2008 dem 51
usa 1/1/2012 dem 50

I want to create a new column, called, votelagged, which calculates the difference between one vote and the previous vote so that it would read:
null
1
2
-1

Various statistical packages do this as a built-in feature, but I can't figure out how to do it manually. Thanks for the consideration.
Tom

Hi,

You need to create a self-outer join query. Then connect the current election with the previous election. See the code below. Keep in mind that this SQL statement cannot be shown in design view, only in SQL view, because of the joinexpression.

HTH, Wen

Great. Forgive me, I should have included the full problem--if there are dozens of countries, hundreds of elections and thousands of parties, such that the increment is not always 4 years between elections and matches must be made on country and party. The table looks like this (and the new variable is last)
Country Election Party Vote VoteDiff
usa 2000 dem 48 null
usa 2000 rep 47 null
usa 2004 dem 49 1
usa 2004 rep 51 4
can 2000 lib 43 null
can 2000 con 41 null
can 2002 lib 42 -1
can 2002 con 38 -3
Thanks again,
Tom