Calculate Absolute difference between rows in MsAccess

I have spent all morning on this and just can't get it right... I'd really appreciate the help of someone more knowledgable than myself to get this working.

I have a table with some data in that looks like this:

MonthYear WeekBeg.  Week  Value  
Dec-10  27/12/2010  1   66.66  
Jan-11  3/01/2011   2   50  
Jan-11  10/01/2011  3   17.5  
Jan-11  17/01/2011  4   20  
Jan-11  24/01/2011  5   0  
Jan-11  31/01/2011  6   50  
Feb-11  7/02/2011   7   0  
Feb-11  14/02/2011  8   74  
Feb-11  21/02/2011  9   100  

I'm sorry the table above doesn't look better... I need to calculate the difference between the values from week to week - so the results column in this case would be:

16.66

32.5

2.5

20

50

50

74

26

I've looked at lots of code on the net - (e.g. from this site) but can't seem to make it work. I added in the ABS function to make sure the differences were absolute values and got this working but the numbers themselves just aren't right.

I haven't posted what I ended up with as it just got into a bigger and bigger mess, but what I started with was the link above. Again, I'd be really grateful for any insight anyone is able to offer.

Many thanks

ADDED:

Thanks so much for the fast reply. Got this working easily - added a few bits:

SELECT T1.MonthYear AS [From], T2.MonthYear AS [To], T1.Week AS Week, T1.WeekBeg AS WeekBeg, ABS(T1.Value - T2.Value) AS Difference FROM Test AS T1 LEFT JOIN Test AS T2 ON T2.Week = T1.Week + 1

Only thing is the resulting difference values need to be in the second of the two rows whereas here they are in the first of the two. Is there any easy way of modifying this?

Many thanks again.

ADDED:

Would definitely be worth using the second option if possible as can't always gu开发者_开发百科arantee weeks won't be missed out. I am probably missing something, but when I run the second option from Thomas, I get the message:

'The specified field [T1].[Datavalue] could refer to more than one table listed in the FROM clause of your SQL statement'.

I thought this might be to do with the field in the table being VALUE not DataValue, but when I change it, I get 'Type Mismatch in Expression' instead.

Many thanks.

Presuming the Week column is perfectly sequential:

Select T1.MonthYear As T1Year
    , T1.WeekBeg As T1WeekBeg
    , T2.MonthYear As T2Year
    , T2.WeekBeg As T2WeekBeg
    , [T2].[Value]-[T1].[Value] AS Expr1
From TableWithData AS T1 
    Left Join TableWithData AS T2 
        On T1.Week = T2.Week + 1;

It should be noted that this will not compile in the QBE designer. You will have to view and modify it purely through the SQL View (or in code)

If for some reason you could not depend on the Week number being sequential, then it gets trickier as you need to use a derived table. Again, this solution will only work in SQL View or in code:

Select T1.MonthYear, T1.WeekBeg
    , T2.MonthYear, T2.WeekBeg
    , [T2].[Value]-[T1].[Value] AS Diff
From (TableWithData AS T1 
    Inner Join (
            Select T1.WeekBeg As T1WeekBeg
                    , Min(T2.WeekBg) As T2WeekBeg
                From TableWithData As T1 
                    Left Join TableWithData AS T2 
                        On T2.WeekBeg > T1.WeekBeg
                Group By T1.WeekBeg
                ) As Query1
        On T1.WeekBeg = Query1.T1WeekBeg) 
    Inner Join TableWithData AS T2 
        On Query1.T2WeekBeg = T2.WeekBeg;

A version based off of the sample query from your base link. (It uses ORDERY BY on the Week field and TOP 1 too isolate a scalar value.)

SELECT  t1.Value - (SELECT TOP 1 t2.Value FROM myTable AS t2 
                               WHERE t2.Week < t1.Week
                               ORDER BY t2.Week DESC) AS t2Val
FROM myTable t1
WHERE (SELECT TOP 1 t3.Value FROM myTable AS t3
       WHERE t1.Week < t3.Week) Is Not Null
ORDER BY t1.Week;

Should be close to working but the aliasing is very error prone. I suggest that if the week numbers are indded sequential that you go with Thomas' answer.