Expression Builder : Query with calculation

[El-d]

Hi All,
I'm having problems getting calculated query to work.

At the moment, my query returns 3 values.
date, close, adjustment
This would look like

2015-04-10 1170 1165
2015-04-13 1163 1158
2015-04-14 1167 1162
2015-04-15 1154 1149
2015-04-16 1137 1132
2015-04-17 1127 1122
2015-04-21 1195 1190
2015-04-22 1201 1196
2015-04-23 1190 1190
2015-04-24 1186 1186

What I am trying to do is add a new field that has the following calculated data.

(close/adjusted)* last value of adjusted

In this case : looking at the last 5 records:-
(1127/1122)*1186
(1195/1190)*1186
(1201/1196)*1186
(1190/1190)*1186
(1186/1186)*1186

My trouble is that I can't get the last value to use in my equation. I tried Dlast but just get pop ups.

Do I have to create a separate query to get my last value so that I can use it in an equation? ..or am I just being a bit thick over this :banghead:

cheers,

El-d

 

[plog]

Do I have to create a separate query to get my last value so that I can use it in an equation?

Technically no, but you should. The most efficient way to do this is with sub-queries--plural. Technically you could use a DMax inside a Dlookup to get that 1186, but that's going to be a resource hog.

Here's how I would do it. Build a query to get the Maximum date:

SELECT MAX([date]) AS LastDate FROM YourTableNameHere;

I'd call that LastDateQuery. Then I would use that in another query linking back to YourTableNameHere to get the adjustment value that matches that LastDate value:

SELECT adjustment FROM YourTableNameHere 
INNER JOIN LasteDateQuery ON LastDate=[date];

Call that LastAdjustmentQuery. Then you could use a Dlookup to it in your main function, or you could simply bring that query in and don't JOIN it to anything.

 

[El-d]

Thanks, will try that.

I think my issue is that Im trying to not have multiple queries poping up / open.

Is there any way of running queries as a hidden background process. I did look a while back but never found a reasonable solution. I.e. turn screen refresh off and back on post queries.
Is there a better way?

Cheers,
El-d

 

[El-d]

Actually misunderstood my Dads equation - trouble with spoken equation rather than seeing the brackets

However, I did end up with 2 queries . 1 to get my adjustment value and the other to process.

Thanks for response. Put me on the right track to realise I was calculating the wrong thing
Cheers
El-d