Hi,

I have a table with a lot of stock data in it, and need to do calculations
between adjacent days.

For example, one result that I need to pull from the database is the
difference between two adjacent days closing prices.

Adjacent days are not always 1 day apart, and this is giving me some trouble
with writing a query which doesn't hang my machine.

How do I do this in an optimal fashion? There are thousands of rows in my
table.

Some sample data from the table is below:

ID symbolID quotedate open high low close volume
adjustedClose
1 124 25 November 2003 8778.29 8778.29 8778.29 8778.29
0 8778.29
2 124 26 November 2003 8768.56 8768.56 8768.56 8768.56
0 8768.56
3 124 27 November 2003 8759.74 8759.74 8759.74 8759.74
0 8759.74
4 124 28 November 2003 8770.85 8770.85 8770.85 8770.85
0 8770.85
5 124 01 December 2003 8891.18 8891.18 8891.18 8891.18
0 8891.18
6 124 02 December 2003 8744.28 8744.28 8744.28 8744.28
0 8744.28
7 124 03 December 2003 8818.29 8818.29 8818.29 8818.29
0 8818.29

Thanks,

Anon.

On Sun, 18 Jan 2004 20:08:50 -0000, "Anon" <(E-Mail Removed)>
wrote:

>I have a table with a lot of stock data in it, and need to do calculations
>between adjacent days.
>
>For example, one result that I need to pull from the database is the
>difference between two adjacent days closing prices.
>
>Adjacent days are not always 1 day apart, and this is giving me some trouble
>with writing a query which doesn't hang my machine.
>
>How do I do this in an optimal fashion? There are thousands of rows in my
>table.

A Self Join query joining the table to itself on the SymbolID with a
criterion on the date of

=DMax("[Quotedate]", "[yourtable]", "[Quotedate] < #" & [Quotedate] &
"#")

Be sure that Quotedate is indexed (nonuniquely). Even so, don't expect
this to run very fast!

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public