Trying to calculate the change from a value in one row from anothe

[Burton Hess]

I am new to programming, please forgive my ignornance.

I have a created a query that uses the following record convention:

record -
date, name, value

recordset-
date, name, value
date1, name1, value1
date2, name2, value2
....etc

My question is: How do I write an expression that creates a new field with
the result of value-value1, (next row) value1-value2, (next row) ...etc?

I would greatly appreciate any help anyone could offer.

Thx Burton

 

[Marshall Barton]

I am new to programming, please forgive my ignornance.

I have a created a query that uses the following record convention:

record -
date, name, value

recordset-
date, name, value
date1, name1, value1
date2, name2, value2
...etc

My question is: How do I write an expression that creates a new field with
the result of value-value1, (next row) value1-value2, (next row) ...etc?

One way is to use a subquery:

Difference: (SELECT Top 1 value FROM thetable As T
WHERE T.date < thetable.date ORDER BY date DESC)

 

[Burton Hess]

One way is to use a subquery:

Difference: (SELECT Top 1 value FROM thetable As T
WHERE T.date < thetable.date ORDER BY date DESC)

Marsh-

Thanks for the quick response, it is awesome.

I don't totally understand the function but I am going to give it a try.

Do I need to create this function in a module or can I use it in an
expression in a field cell?

Thanks again. BH

 

[Burton Hess]

Ok. I tried the function in both a query and module, neither one executes.
I get the logic but the "difference:" statement does not seem to exist. Can
you help me out?

Thanks again.

 

[Marshall Barton]

:
I don't totally understand the function but I am going to give it a try.

Do I need to create this function in a module or can I use it in an
expression in a field cell?

That is not a function.

It's calculated field in the query (the calculation uses an
SQL statenment). So, put it in the Fields row in a blank
column of your query.

 

[Marshall Barton]

You probably did it wrong. Open your query in design view,
switch to SQL view and Copy/Paste it here so I can see what
you have.

 

[Burton Hess]

Here it is:

SELECT test.date, test.ticker, test.rsi, (Select Top 1 rsi From test As T
Where T.date < test.date Order By date DESC) AS difference
FROM test
GROUP BY test.date, test.ticker, test.rsi, (Select Top 1 rsi From test As T
Where T.date < test.date Order By date DESC)
ORDER BY test.ticker;

Where date is date, test is table and rsi is value.

I just get a syntax error when I try to run the query.

Marsh, Your help is greatly appreciated!!!

You probably did it wrong. Open your query in design view,
switch to SQL view and Copy/Paste it here so I can see what
you have.
--
Marsh
MVP [MS Access]

Ok. I tried the function in both a query and module, neither one executes.
I get the logic but the "difference:" statement does not seem to exist. Can
you help me out?

Thanks again.

 

[Marshall Barton]

SELECT test.date, test.ticker, test.rsi, (Select Top 1 rsi From test As T
Where T.date < test.date Order By date DESC) AS difference
FROM test
GROUP BY test.date, test.ticker, test.rsi, (Select Top 1 rsi From test As T
Where T.date < test.date Order By date DESC)
ORDER BY test.ticker;

Where date is date, test is table and rsi is value.

I just get a syntax error when I try to run the query.

I can't be sure what it's complaining about.

First, using a field named Date could be the problem. Date
is the name of a commonly used function and it's impossible
to determine if you're referring to the field or the
function. Change the name of the field to something else.

Second, the GROUP BY clause looks useless, get rid of it.
The only reason for using GROUP BY without also using an
aggregate function is to eliminate duplicate records in the
resulting dataset. If that is necessary, use the DISTINCT
predicate instead.

Third, the subquery refers to some fields without using a
table name to qualify the field, so it's possible that the
subquery will pick up field values from the main query.

Let's try this (changing the name of the date field to
Adate)

SELECT DISTINCT test.Adate, test.ticker, test.rsi,
(Select Top 1 T.rsi From test As T
Where T.Adate< test.Adate
Order By T.Adate DESC) AS difference
FROM test
ORDER BY test.ticker

Be sure to check if you really need to use DISTINCT. It
takes a lot of processing and may slow things down.

 

[Burton Hess]

Marsh-

First of all I am not complaining. I am truely truely greatful for your help.

We are getting closer. I made your suggested changes and had to enclose
the expression in quotes and the "The Syntax of the subquery in this
expression is incorrect" error message was fixed. Yeah!

Now I get this error when executing the query with the ! key -

"You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause. Revise the
SELECT statement of the subquery to request only one field. (Error 3306)
This is an unexpected error. Please contact Microsoft Product Support
Services for more information."

By the way I removed the DISTINCT statement as I do not belive I need the
Group By function.

I will continue to play with the code. Thanks again you are really great.
I am interested in getting your thoughts on the new error message as it is my
next issue to resolve.

 

[Marshall Barton]

Please post your version of the query after every change so
we can see what you actually have.

Adding quotes around the expression won't help and will make
the expression useless.

That error doesn't quite make sense, but it does highlight a
potential problem. Top 1 is not guaranteed to return a
single row, so it can't work. Brain Fault, shame on me :-(

Now I seem to have hit a wall on this, but maybe this really
slow mess will be adequate(?)

SELECT test.Adate, test.ticker, test.rsi,
DLookup("rsi", "test", "Adate = #" & DMax("Adate",
"test", "Adate < #" & test.Adate & "#") & "#")
) AS difference
FROM test
ORDER BY test.ticker

I think I'm getting lost in the messiness of that, so no
guarantees.

Sure would be nice if someone else has a better idea.