Calculation using records of a DAO.recordset

[Richard1980]

Hello,
I have a table where are stored market prices for different securities. This table is loaded as subform in another form and shows all price data for a given security. In the subform I insert new prices and a textbox automatically calculate the price change (on the previous day)
No problem about the calculation when I am on the last record of the recordset, but the problem comes up when I have to insert prices for past dates. I might have this situation:

Date - Price - Change
01/09/2014 - 100 - n/a
02/09/2014 - 101 - +1,00%
03/09/2014 - (price not inserted yet)
04/09/2014 - (price not inserted yet)
05/09/2014 - 101,50 - n/a
08/09/2014 - 101,50 - unch.

On Sept. 9th I want to insert prices for the days 03 and 04 and get this calculation of daily price change on Sept. 3rd, 4th and 5th.
Do you have any suggestion?
Many thanks.
Bye.

R. Righetti

 

[vss]

My suggestion: you can do 2 steps:
- Update price for days 3 and 4 (UPDATE sql)
- Recalculate to UPDATE changed value from first row by built a new sub to do.

 

[CJ_London]

Assuming your table consists of three fields for the purposes of this example

Security PDate Price

SELECT *, (SELECT TOP 1 Price/PriceTable.Price from PriceTable AS T WHERE Security=PriceTable.Security AND PDate=PriceTable.Pdate-1) AS Change
FROM PriceTable

This assumes that there are no breaks for weekends, public holidays etc.

Another way to do it is as follows

SELECT PriceTable.*, iif(nz(PriceTable_1.Price,0)<>0,PriceTable.Price/PriceTable_1.Price) As Change
From PriceTable Inner Join PriceTable as PriceTable_1 ON PriceTable.Security=PriceTable_1.Security
WHERE PriceTable_1.PDate=(SELECT Max(PDate) FROM PriceTable as T WHERE Security=PriceTable.Security AND Pdate<PriceTable.Pdate)

This code will 'leap' the weekends but in the case of your example would also provide a value for 05/09/2014.

But you could modify either of the above to further exclude if required