SQL advice needed 1

[teach314]

I have a table (tblDATA) with autonumber PK and all other values are integers.
The data shown below is a simplified version of a much larger table.

What I need to do is create a stored query (qryDATA).

tblDATA qryDATA

PK t1 t2 t3 t4 K q1 q2 q3 q4
1 0 0 0 0 1 0 0 0 0
2 0 4 2 0 2 0 4 2 0
3 0 -2 3 -5 3 0 6 1 5
4 4 -1 0 3 4 4 1 3 8
5 2 0 -3 0 5 2 1 3 3
6 0 0 0 0 6 2 0 3 0

Here's the rule: In column t3, for example, we move down the column finding
the Absolute Value of the difference between each value and the one above it.
These produce the values in column q3

1 0
2 ABS( 2 - 0) = 2
3 ABS( 3 - 2) = 1
4 ABS( 0 - 3) = 3
5 ABS(-3 - 0) = 3
6 ABS( 0 - -3) = 3

Here's the Question: Getting the values for qryDATA is elementary in Excel, and
I am able to use VBA to create the values for qryDATA. But, because the tables
are large, I'd like to try generating values in bulk using SQL.

I would really appreciate anyone who can point me in the right direction. I just need some insight in how to go about doing this.

 

[SkipVought]

something like this...

SELECT A.PK, abs(B.t1-A.t1), abs(B.t2-A.t2), abs(B.t3-A.t3), abs(B.t4-A.t4)

FROM 
  DTA A
, DTA B

Where A.PK = B.PK+1

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[teach314]

Skip - that is REALLY helpful. It works beautifully and is much shorter and faster than my VBA attempt. The only minor difficulty is that is that the query is missing the top row (1,0,0,0,0) because it has no 'previous' row. Is there a way to include this row in the query? Should I use a Union Query to attach the first row? (I'm quite new to SQL)

 

[SkipVought]

[b]
SELECT A.PK, A.t1, A.t2, A.t3, A.t4

FROM 
  DTA A

Where A.PK = 1

UNION ALL
[/b]
SELECT A.PK, abs(B.t1-A.t1), abs(B.t2-A.t2), abs(B.t3-A.t3), abs(B.t4-A.t4)

FROM 
  DTA A
, DTA B

Where A.PK = B.PK+1

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[teach314]

thanks Skip - that works great!