Price variation over the years

[El_Pablo]

Hi,

I need to do a statistical calculation in a table. I need to calculate the selling price variation of the same house over each sells.

By example we have a sold table which is related to himself with a field called fullAddress.

A house can be sold many times. I want to calculate the price fluctuation from the previous sell of this house.

By example:

House on 123 Fake street

Date Price Fluctuation

2006/12/10 125 000$ 25%

2004/06/06 100 000$ 25%

2000/02/13 80 000$ N/A

Need help please...

Thank you

Edited February 19, 2007 by Guest

[mr_vodka]

It seems like you should be able to do it with a calc such as:

Amount / GetNthRecord ( fullAddressSJ::Amount; Get ( RecordNumber ) + 1) - 1

If you have the relationship sorted Descending by Sold Date.

P.S. I havent tested it.

[El_Pablo]

Hmmm... I get an error.

Since the value is from a related record, will I get a wrong value? Because the function Get (RecordNumber) returns the record in the current found set and not from the related record?

I'm trying to figure out a way, but I'm missing some functions...

[El_Pablo]

Thank you very much it work. I had to view the field in a portal.

Here's my the solution:

If (Get (RecordNumber) ≠ Get ( FoundCount );

PRICE / GetNthRecord ( Sells::PRICE; Get(RecordNumber) + 1) - 1;

/*else*/

"")

[mr_vodka]

See here

Fluctuation.zip

[mr_vodka]

BTW, I am not sure why you have all this data in one table. IMO, there should be at least two. One to store the house info, and the other to store the sales transactions.

[El_Pablo]

Don't tell I know... The DB has been created in the mid-90s by someone that didn't had any database knowledge and was updated with new fields since then...

Can you imagine 12 years updating a flattable by adding fields here and there without knowing the basics of normalization! A nightmare for any DB programmer. When I saw the structure for the first time I wanted to cry! There are mainly 2 tables with a multitude of small tables. One with 1800+ fields and the second with 1400+ fields! (This is not a typing error)

I'm working on a whole new db that will be transfered on SQL Server.