How to create a new field from other record fields in a table

[FSHOTT]

Can someone show me how to create a field in a table or query which is made
up of other fields in the in the current and previous records? That is say I
have field TypeID in a Table and I would like to add a new field DeltaTypeID
which compares (=) TypeID from record x to TypeID from record x-1.

 

[KARL DEWEY]

You did not say much about your table structure.
The trick is being able to select the previous record.
Your query you put the table twice and Access adds a sufix of '_1' on the
name of the second instance of the table. You have to use information from
one and compare with the other such as autonumber minus one. You can also
work with dates by selecting the first date lower or higher than the other
table record.

 

[NTC]

SELECT TableName.KeyField, TableName.TypeID, (TableName.TypeID-T2.TypeID) AS
DeltaTypeID
FROM TableName LEFT JOIN TableName AS T2 ON TableName.KeyField-1=T2.KeyField;

 

[FSHOTT]

What is the TableName.TypeID-T2 ? And what does the
(TableName.TypeID-T2.TypeID ) do in the SELECT statement?

 

[NTC]

replace TableName with your actual table name

T2 is literal - it needs to be there as the alias of the duplicate table

It is not: TableName.TypeID-T2

It is math: TableName.TypeID-T2.TypeID

This is the delta calculation of the TypeID field

 

[FSHOTT]

Yes That worked - Great Thank You very much.....