Difference between records

[1234adam]

I have 8 fields and i would like to make 8 more fields each one being the change in a field.

I would like my table to look like this

Date..........Field1..........Change..........Field2..........Change
1-1..............5..........................................7.............
1-2..............10................5.......................13...........6
1-3..............6.................-4.......................8...........-5

How do I calculate [Change]

Any suggestions

 

[The_Doc_Man]

Well, the EASIEST way for me would be with a VBA module, but some folks don't like that. The problem in doing this with an UPDATE query is that old bugaboo that is SQL. You see, it ain't called SEQUENTIAL query language for nothin' - each record in the recordset is a unique and independent entity as far as SQL is concerned.

BTW, I hope you aren't really using 'DATE' as a field name. It is a reserved word.

The way some folks do this is to run a couple of preparatory steps.

In step 1 you add a record number field to your table, then number your records sequentially from 1. NOT an autonumber. Looks to me like your dates are unique. If so, use them as a key. You would make each record number equal to 1 + DSum("[datefield],"MyTable","[MyTable].[datefield] < #" & [datefield] & "#") - or something similar.

OK, in step 2, you can compute the difference between your two successive records 'cause now you have numbered them and can KNOW which one is the previous record. There, you would use something like [field1] - NZ( DLookup("[Field1]","MyTable","[recnum] = " & Str( [Recnum] - 1 ) ), 0 )

The idea is that you take the NZ function of the corresponding field from the - now identifiable - previous record, unless this is record 1 and there IS no previous record. But the trick is to make the records identifiable first.