Calculation based on Query Row or Record Location

[Kaloyanides]

I'm trying to do a series of calculations in an access query.



It's to calculate the % change of stock prices.

For each stock, there are almost 4,000 records. It's the closing price for each day going back to 1999.

Table
Date Symbol Price
11/18/1999 AA 41.17

I'm trying to create the following for Query Record each symbol:

Symbol 5 Day %Change 10 Day %Change 30 Day %Change 60 Day %Change 90 Day %Change 6 Mo %Change 1 Yr %Change 2 Yr %Change 3 Yr %Change 4 Yr %Change 5 Yr %Change etc....

The Query needs to calculate the % change between the most recent Previous Close Price and the preceding 5th record, 10th record, 30th record, 60th record, etc...

I hope I'm explaining correctly.

I can't figure out how to write the following formula:
5DayChange : =([PreviousClose]-[5 Records Back in the Table)/ABS([5 Records Back in the Table)
10DayChange : =([PreviousClose]-[10 Records Back in the Table)/ABS([10 Records Back in the Table)

Or, should I be trying to do this by defining a specific date to use in each calculation?

Thx in advance!

[ranman256]

You can't. You must calc the on day X in 1 qry.
then another qry for day X-5
another qry , day x-10

but not record, unless they are sequentually numbered
note: a record cannot see its neighbor.

[June7]

Options for each record to pull value from another record of same table:

1. multiple queries ranman describes

2. nested subquery - review http://allenbrowne.com/subquery-01.html#AnotherRecord

3. domain aggregate function - DLookup, DMax, DMin