help with query

[Bob M]

Hi
I have an Access query named Query1


It has the following fields
(1) datetime
(2) Close_EURUSD
(3) MovingAverage [which is a 20 day MA of field (2)

I wish to add a fourth field (calculated) named Trend

The formula I wish to use is as follows:-
From 101st record onwards, if (field(3) of 101st record - field(3) of 21st record) < -0.00005 then trend = 0
else if (field(3) of 101st record - field(3) of 21st record) > 0.00005 then trend = 1
else trend = 2

Unsure how to state this formula in the 4th field position on the Query1 view ?

Bob M

[ranman256]

the formula can only calculate the record you are on. fld3 of rec1 minus fld4 of rec1.
it cannot use field3 of record 101 and field3 of record#21.

unless you have X queries, 1 that pulls rec101, 1 that pulls rec21...

you need another option, maybe a custom function.

[ssanfu]

I have an Access query named Query1
It has the following fields
(1) datetime

If you have a field in a table that is named "datetime", you shouldn't. "datetime" is a reserved word in Access and shouldn't be used for object names. Plus it is not very descriptive. Maybe use "dtTrade" or "TradeDateTime".

Since Access doesn't have record numbers, the 21st or the 101st records are arbitrary. There is no assurance that a record will have the same absolute position if the Recordset object is re-created again because the order of individual records within a Recordset object isn't guaranteed unless it's created with an SQL statement by using an ORDER BY clause.

Will there ALWAYS be an 80 record difference between the records used to calc the trend?
101 -> 21
102 -> 22
103 -> 23


You might be able to write an UDF to do the calculation. Without your dB or some test data, I can't give a better suggestion.

[Bob M]

If you have a field in a table that is named "datetime", you shouldn't. "datetime" is a reserved word in Access and shouldn't be used for object names. Plus it is not very descriptive. Maybe use "dtTrade" or "TradeDateTime".

Since Access doesn't have record numbers, the 21st or the 101st records are arbitrary. There is no assurance that a record will have the same absolute position if the Recordset object is re-created again because the order of individual records within a Recordset object isn't guaranteed unless it's created with an SQL statement by using an ORDER BY clause.

Will there ALWAYS be an 80 record difference between the records used to calc the trend?
101 -> 21
102 -> 22
103 -> 23


You might be able to write an UDF to do the calculation. Without your dB or some test data, I can't give a better suggestion.

Thanks for your input, Steve

I have renamed the field concerned and gone back to excel to accomplish what I want
Cheers
Bob M

[ssanfu]

Good luck with your project...... sorry I couldn't help more.