Calculating values defined by their #

[laurentc]

Dear all,
I have a simple table, with only the following fields:
- Key
- MyDate
- Price
I would like to biuld a Query which uses these fields and which make some
other calculations on the Price.

To do one calculation, I need to get the difference between the last two
"Price", to get a "ModifiedPrice".

This difference would be this one:
Difference = "LastPrice" - "PenultimatePrice"

Then my ModifiedPrice would be simply equal to Price + Difference...

1. Could you help me finding the good way to do this rather simple
calculation, either directly in my query, or through a function?

2. I also would like to be able to calculate the percentage difference of the
prices.
The calculation would be:
Return : (Price / Price[-1] ) -1

However, I do not know how to find the number "Price[-1]" in Access.
Thanks a lot for your help.

Regards,

Laurent

[Smartin]

laurentc wrote:

Dear all,
I have a simple table, with only the following fields:
- Key
- MyDate
- Price
I would like to biuld a Query which uses these fields and which make some
other calculations on the Price.

To do one calculation, I need to get the difference between the last two
"Price", to get a "ModifiedPrice".

This difference would be this one:
Difference = "LastPrice" - "PenultimatePrice"

Then my ModifiedPrice would be simply equal to Price + Difference...

1. Could you help me finding the good way to do this rather simple
calculation, either directly in my query, or through a function?

2. I also would like to be able to calculate the percentage difference of the
prices.
The calculation would be:
Return : (Price / Price[-1] ) -1

However, I do not know how to find the number "Price[-1]" in Access.
Thanks a lot for your help.

Regards,

Laurent

This might do the trick. I used the following sample data:

KEY MYDATE PRICE
1 1/1/2006 $10.00
2 2/1/2006 $15.00
3 3/1/2006 $14.00 (*)
4 4/1/2006 $23.00 (*)
5 2/5/2006 $18.00

(*) = the last two prices

SELECT (FIRST(THEPRICE)-LAST(THEPRICE)) AS DIFFERENCE
FROM (SELECT TOP 2 PRICE AS THEPRICE FROM PRICES ORDER BY MYDATE DESC);

Returns: $9 (= $23 - $14)

SELECT (FIRST(THEPRICE)/LAST(THEPRICE)-1) AS PERCENTAGE
FROM
(SELECT TOP 2 PRICE AS THEPRICE FROM PRICES ORDER BY MYDATE DESC);

Returns: 0.64285714286 (= 23/14 - 1)

Side note: It's probably something wrong with my DB but when I save
these queries and open them up again, they turn into something like this:

SELECT (FIRST(THEPRICE)-LAST(THEPRICE)) AS DIFFERENCE
FROM [SELECT TOP 2 PRICE AS THEPRICE FROM PRICES ORDER BY MYDATE DESC].
AS [%$##@_Alias];

Weird.

HTH
--
Smartin

[Smartin]

Smartin wrote:

Side note: It's probably something wrong with my DB but when I save
these queries and open them up again, they turn into something like this:

SELECT (FIRST(THEPRICE)-LAST(THEPRICE)) AS DIFFERENCE
FROM [SELECT TOP 2 PRICE AS THEPRICE FROM PRICES ORDER BY MYDATE DESC].
AS [%$##@_Alias];

Following up on my last comment, and OT to the thread, it seems Access
wants to create an alias for the subquery.

The following works:
SELECT (FIRST(THEPRICE)-LAST(THEPRICE)) AS DIFFERENCE
FROM (SELECT TOP 2 PRICE AS THEPRICE FROM PRICES ORDER BY MYDATE DESC)
AS SUBQ;

Though Access changes it to:
SELECT (FIRST(THEPRICE)-LAST(THEPRICE)) AS DIFFERENCE
FROM [SELECT TOP 2 PRICE AS THEPRICE FROM PRICES ORDER BY MYDATE DESC].
AS SUBQ;

Still a little odd, methinks. I don't recall seeing Access change
parentheses to square brackets around a subquery before. Also note
Access inserts a dot after the subquery and before the alias.

--
Smartin

[laurentc via AccessMonster.com]

Smartin, that SUBQUERY works fine.

Thanks a lot for your help.
Regards,

Laurent

Smartin wrote:

>Side note: It's probably something wrong with my DB but when I save
these queries and open them up again, they turn into something like this:

SELECT (FIRST(THEPRICE)-LAST(THEPRICE)) AS DIFFERENCE
FROM [SELECT TOP 2 PRICE AS THEPRICE FROM PRICES ORDER BY MYDATE DESC].
AS [%$##@_Alias];

Following up on my last comment, and OT to the thread, it seems Access
wants to create an alias for the subquery.

The following works:
SELECT (FIRST(THEPRICE)-LAST(THEPRICE)) AS DIFFERENCE
FROM (SELECT TOP 2 PRICE AS THEPRICE FROM PRICES ORDER BY MYDATE DESC)
AS SUBQ;

Though Access changes it to:
SELECT (FIRST(THEPRICE)-LAST(THEPRICE)) AS DIFFERENCE
FROM [SELECT TOP 2 PRICE AS THEPRICE FROM PRICES ORDER BY MYDATE DESC].
AS SUBQ;

Still a little odd, methinks. I don't recall seeing Access change
parentheses to square brackets around a subquery before. Also note
Access inserts a dot after the subquery and before the alias.

--
Message posted via http://www.accessmonster.com