calculate a new value from two values in the same field

[Guest]

I have a tracking program that outputs 2 columns ([x] and [Y]) that represent
the position of an object on a 2D plane through time. Time is designated by a
third column [frame] which can have a value from 1 to however many frames
were tracked (n). I need to calculate new fields that would be equal to
(x for [frame]=2) - (x for [frame]=1), (x for [frame]=3) - (x for
[frame]=2), up to (x for [frame]=n) - (x for [frame]=n-1), and (y for
[frame]=2) - (y for [frame]=1), (y for [frame]=3) - (y for [frame]=2), up to
(y for [frame]=n) - (y for [frame]=n-1) (so that i can use Pythagoras' theorm
to caluclate the distance the object has moved between each time step). The
number of frames tracked (n) is different for different objects.
Does anyone have any idea how to do this in a query?

 

[Guest]

Hi,

You can do this using DLookup or Correlated Subqueries. If your data set
isn't too large DLookup may be the simplest. Try the following to look up
the previous x:

PrevX: DLookup("[x]","YourTableName","[frame] = " & [frame]-1)

Where you would need to use your actual table name. Note that this assumes
that you don't have any gaps in the frame number. If you do you would need
to change the criteria such as replacing [frame]-1 with:

DMax("[frame]","YourTableName","[frame] < " [frame])

Also, I believe this will return null for the first record. You can use the
Nz() function to replace the Null result with whatever you wish (such as 0).
In that case it would become:

PrevX: Nz(DLookup("[x]","YourTableName","[frame] = " & [frame]-1),0)

You could look up the y value the same way, just replace x with y.

HTH, Ted Allen

 

[Guest]

Thanks heaps ted,
works a treat!

 

[Guest]

Hi Ted,
If you are still reading this would it be possible to run through how to do
this same thing with correlated subqueries. dlookup works fine, but because
the dataset is quite large, my queries take several minutes to run...

Hi,

You can do this using DLookup or Correlated Subqueries. If your data set
isn't too large DLookup may be the simplest. Try the following to look up
the previous x:

PrevX: DLookup("[x]","YourTableName","[frame] = " & [frame]-1)

Where you would need to use your actual table name. Note that this assumes
that you don't have any gaps in the frame number. If you do you would need
to change the criteria such as replacing [frame]-1 with:

DMax("[frame]","YourTableName","[frame] < " [frame])

Also, I believe this will return null for the first record. You can use the
Nz() function to replace the Null result with whatever you wish (such as 0).
In that case it would become:

PrevX: Nz(DLookup("[x]","YourTableName","[frame] = " & [frame]-1),0)

You could look up the y value the same way, just replace x with y.

HTH, Ted Allen

I have a tracking program that outputs 2 columns ([x] and [Y]) that represent
the position of an object on a 2D plane through time. Time is designated by a
third column [frame] which can have a value from 1 to however many frames
were tracked (n). I need to calculate new fields that would be equal to
(x for [frame]=2) - (x for [frame]=1), (x for [frame]=3) - (x for
[frame]=2), up to (x for [frame]=n) - (x for [frame]=n-1), and (y for
[frame]=2) - (y for [frame]=1), (y for [frame]=3) - (y for [frame]=2), up to
(y for [frame]=n) - (y for [frame]=n-1) (so that i can use Pythagoras' theorm
to caluclate the distance the object has moved between each time step). The
number of frames tracked (n) is different for different objects.
Does anyone have any idea how to do this in a query?

 

[Guest]

Sure,

For PrevX try the following (air code, let me know if it doesn't work and
I'll recheck the syntax):

PrevX: (SELECT First([x]) FROM YourTableName AS VT
WHERE VT.[frame] = (YourTableName.[frame]-1))

Subqueries used in this manner must always be written such that they only
return one value, which is why I used First() even though we know there will
only be one match (Jet doesn't know that). VT is an alias for a second
instance of the table (VT is commonly used - meaning Virtual Table, but you
can use any alias). The alias is needed to relate the value of the field in
the current record to the value you are matching to in the second instance of
the table.

Hope that helps. Post back if you have any problems or any other questions.

-Ted Allen

Hi Ted,
If you are still reading this would it be possible to run through how to do
this same thing with correlated subqueries. dlookup works fine, but because
the dataset is quite large, my queries take several minutes to run...

Hi,

You can do this using DLookup or Correlated Subqueries. If your data set
isn't too large DLookup may be the simplest. Try the following to look up
the previous x:

PrevX: DLookup("[x]","YourTableName","[frame] = " & [frame]-1)

Where you would need to use your actual table name. Note that this assumes
that you don't have any gaps in the frame number. If you do you would need
to change the criteria such as replacing [frame]-1 with:

DMax("[frame]","YourTableName","[frame] < " [frame])

Also, I believe this will return null for the first record. You can use the
Nz() function to replace the Null result with whatever you wish (such as 0).
In that case it would become:

PrevX: Nz(DLookup("[x]","YourTableName","[frame] = " & [frame]-1),0)

You could look up the y value the same way, just replace x with y.

HTH, Ted Allen

I have a tracking program that outputs 2 columns ([x] and [Y]) that represent
the position of an object on a 2D plane through time. Time is designated by a
third column [frame] which can have a value from 1 to however many frames
were tracked (n). I need to calculate new fields that would be equal to
(x for [frame]=2) - (x for [frame]=1), (x for [frame]=3) - (x for
[frame]=2), up to (x for [frame]=n) - (x for [frame]=n-1), and (y for
[frame]=2) - (y for [frame]=1), (y for [frame]=3) - (y for [frame]=2), up to
(y for [frame]=n) - (y for [frame]=n-1) (so that i can use Pythagoras' theorm
to caluclate the distance the object has moved between each time step). The
number of frames tracked (n) is different for different objects.
Does anyone have any idea how to do this in a query?

 

[Guest]

In looking back at my previous post, I should have explicitly defined X in
the subquery. I'm not sure whether it would be required, but I generally
like to do that in subqueries to make them more clear. Also, the brackets
around the field names are not required since you do not have spaces, but I
usually include them as a reminder that they are required when spaces do
exist. Following is the revised text of the subquery:

PrevX: (SELECT First(VT.[x]) FROM YourTableName AS VT
WHERE VT.[frame] = (YourTableName.[frame]-1))

HTH, Ted Allen

Sure,

For PrevX try the following (air code, let me know if it doesn't work and
I'll recheck the syntax):

PrevX: (SELECT First([x]) FROM YourTableName AS VT
WHERE VT.[frame] = (YourTableName.[frame]-1))

Subqueries used in this manner must always be written such that they only
return one value, which is why I used First() even though we know there will
only be one match (Jet doesn't know that). VT is an alias for a second
instance of the table (VT is commonly used - meaning Virtual Table, but you
can use any alias). The alias is needed to relate the value of the field in
the current record to the value you are matching to in the second instance of
the table.

Hope that helps. Post back if you have any problems or any other questions.

-Ted Allen

Hi Ted,
If you are still reading this would it be possible to run through how to do
this same thing with correlated subqueries. dlookup works fine, but because
the dataset is quite large, my queries take several minutes to run...

Hi,

You can do this using DLookup or Correlated Subqueries. If your data set
isn't too large DLookup may be the simplest. Try the following to look up
the previous x:

PrevX: DLookup("[x]","YourTableName","[frame] = " & [frame]-1)

Where you would need to use your actual table name. Note that this assumes
that you don't have any gaps in the frame number. If you do you would need
to change the criteria such as replacing [frame]-1 with:

DMax("[frame]","YourTableName","[frame] < " [frame])

Also, I believe this will return null for the first record. You can use the
Nz() function to replace the Null result with whatever you wish (such as 0).
In that case it would become:

PrevX: Nz(DLookup("[x]","YourTableName","[frame] = " & [frame]-1),0)

You could look up the y value the same way, just replace x with y.

HTH, Ted Allen

:

I have a tracking program that outputs 2 columns ([x] and [Y]) that represent
the position of an object on a 2D plane through time. Time is designated by a
third column [frame] which can have a value from 1 to however many frames
were tracked (n). I need to calculate new fields that would be equal to
(x for [frame]=2) - (x for [frame]=1), (x for [frame]=3) - (x for
[frame]=2), up to (x for [frame]=n) - (x for [frame]=n-1), and (y for
[frame]=2) - (y for [frame]=1), (y for [frame]=3) - (y for [frame]=2), up to
(y for [frame]=n) - (y for [frame]=n-1) (so that i can use Pythagoras' theorm
to caluclate the distance the object has moved between each time step). The
number of frames tracked (n) is different for different objects.
Does anyone have any idea how to do this in a query?