If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to create a new field from other record fields in a table
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. -- frank-a |
#2
|
|||
|
|||
How to create a new field from other record fields in a table
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. -- KARL DEWEY Build a little - Test a little "FSHOTT" wrote: 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. -- frank-a |
#3
|
|||
|
|||
How to create a new field from other record fields in a table
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" wrote: 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. -- frank-a |
#4
|
|||
|
|||
How to create a new field from other record fields in a table
What is the TableName.TypeID-T2 ? And what does the
(TableName.TypeID-T2.TypeID ) do in the SELECT statement? -- frank-a "FSHOTT" wrote: 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. -- frank-a |
#5
|
|||
|
|||
How to create a new field from other record fields in a table
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" wrote: What is the TableName.TypeID-T2 ? And what does the (TableName.TypeID-T2.TypeID ) do in the SELECT statement? -- frank-a "FSHOTT" wrote: 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. -- frank-a |
#6
|
|||
|
|||
How to create a new field from other record fields in a table
Yes That worked - Great Thank You very much.....
-- frank-a "NTC" wrote: 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" wrote: What is the TableName.TypeID-T2 ? And what does the (TableName.TypeID-T2.TypeID ) do in the SELECT statement? -- frank-a "FSHOTT" wrote: 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. -- frank-a |
Thread Tools | |
Display Modes | |
|
|