Comparing Two Adjacement Records

[carl]

I am new to access.

My Data looks like columns a,b,c,d:

Id Date Time Code Difference
1 20060428 93155 53
2 20060428 93155 85 32
3 20060428 93157 86 1
4 20060429 93159 54 -32
5 20060429 93159 55 1
6 20060429 93205 87 32
7 20060429 93205 88 1

I am trying to create Column D - the diference between Code Record 2 and
REcord 1 = 32, Code Record 3 and Record 4 = 1 etc.

Can someone show me the SQL to do this type of thing - if it is possible in
Access.

Thank you in advance.

 

[Allen Browne]

Try typing this into a fresh column of query design, in the Field row.
It is all one line, and replace "Table1" with the name of your table:
Difference: [Table1]

 -
(SELECT TOP 1 Dupe.Code
FROM [Table1] AS Dupe
WHERE Dupe.Id < [Table1].Id
ORDER BY Dupe.Id DESC)

This assumes that Id is the primary key (unique), that the query is sorted
by Id, and that there is no criteria that excludes records.

The query will be read-only (not updatable.)

The calculated field uses a subquery into a duplicate copy of the same table
to get the value from the previous row. If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066